+--------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----+
| Table | Create Table |
+--------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----+
| SmsTest | CREATE TABLE `SmsTest` (
`sn` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增编号',
`phoneNo` int(16) NOT NULL,
`channelType` int(11) DEFAULT NULL COMMENT '通道识别',
`status` tinyint(4) NOT NULL COMMENT '短信转态,1.发送成功,2.发送失败,3.发送异常',
PRIMARY KEY (`sn`)
) ENGINE=InnoDB AUTO_INCREMENT=45209 DEFAULT CHARSET=utf8 COMMENT='短信发送成功记录表' |
+--------- +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----+
1 row in set (0.06 sec) mysql> insert into SmsTest select sn,sn,channelType,status from SmsRecord limit 13;
Query OK, 13 rows affected (0.02 sec)
Records: 13 Duplicates: 0 Warnings: 0 mysql> create index SmsTest_idx1 on SmsTest(phoneNo);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from SmsTest;
+-------+---------+-------------+--------+
| sn | phoneNo | channelType | status |
+-------+---------+-------------+--------+
| 1 | 1 | 2 | 1 |
| 2 | 2 | 2 | 1 |
| 3 | 3 | 2 | 1 |
| 4 | 4 | 2 | 1 |
| 5 | 5 | 2 | 1 |
| 6 | 6 | 2 | 1 |
| 7 | 7 | 2 | 1 |
| 8 | 8 | 2 | 1 |
| 9 | 9 | 2 | 1 |
| 10 | 10 | 2 | 1 |
| 11 | 11 | 2 | 1 |
| 12 | 12 | 2 | 1 |
| 13 | 13 | 2 | 1 |
| 45209 | 16 | 1 | 1 |
| 45210 | 17 | 1 | 1 |
| 45211 | 18 | 1 | 1 |
| 45212 | 19 | 1 | 1 |
| 45213 | 20 | 1 | 1 |
+-------+---------+-------------+--------+
18 rows in set (0.00 sec) Session 1: mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec) mysql> start transaction;
Query OK, 0 rows affected (0.00 sec) mysql> select * from SmsTest where phoneNo between 10 and 20 for update;
+-------+---------+-------------+--------+
| sn | phoneNo | channelType | status |
+-------+---------+-------------+--------+
| 10 | 10 | 2 | 1 |
| 11 | 11 | 2 | 1 |
| 12 | 12 | 2 | 1 |
| 13 | 13 | 2 | 1 |
| 45209 | 16 | 1 | 1 |
| 45210 | 17 | 1 | 1 |
| 45211 | 18 | 1 | 1 |
| 45212 | 19 | 1 | 1 |
| 45213 | 20 | 1 | 1 |
+-------+---------+-------------+--------+
9 rows in set (0.02 sec) Session 2: mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(8,1,1);
Query OK, 1 row affected (0.01 sec) mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(9,1,1); --hang mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(10,1,1);--hang mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(11,1,1); --hang mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(12,1,1); --hang
mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(14,1,1); --hang mysql> insert into zjzc.SmsTest(PhoneNo,channelType,status) values(15,1,1); --hang RR区间锁 不是唯一索引,即使区间内没值,也锁