区间锁

The locks are normally next-key locks that also block inserts into the “gap” immediately before the record. 


这句话怎么理解:


select @@tx_isolation; 


第一种情况 更新列没有索引:

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=1 DEFAULT CHARSET=utf8 COMMENT='短信发送成功记录表'        


模拟数据:
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 |           1 |      1 |
| 16 |      16 |           2 |      1 |
| 17 |      17 |           2 |      1 |
| 18 |      18 |           1 |      1 |
| 19 |      19 |           1 |      1 |
| 20 |      20 |           2 |      1 |
+----+---------+-------------+--------+
15 rows in set (0.00 sec)



Session 1:
mysql> use zjzc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from SmsTest where phoneNo=16 for update;
+----+---------+-------------+--------+
| sn | phoneNo | channelType | status |
+----+---------+-------------+--------+
| 16 |      16 |           2 |      1 |
+----+---------+-------------+--------+
1 row in set (0.00 sec)



Session 2:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into SmsTest values(11,11,1,1);

mysql> insert into zjzc.SmsTest values(11,1,1,1);

mysql> insert into zjzc.SmsTest values(110,110,1,1);

mysql> insert into zjzc.SmsTest values(200,110,1,1);


此时锁全表



在phoneNo 列上加上索引:
mysql>  create index SmsTest_idx1 on SmsTest(phoneNo);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table SmsTestG;
*************************** 1. row ***************************
       Table: SmsTest
Create Table: 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`),
  KEY `SmsTest_idx1` (`phoneNo`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 COMMENT='短信发送成功记录表'
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into zjzc.SmsTest(phoneNo,channelType,status) values(1,1,1);
Query OK, 1 row affected (0.00 sec)

mysql>  insert into zjzc.SmsTest(phoneNo,channelType,status) values(2,1,1);
Query OK, 1 row affected (0.00 sec)

mysql>  insert into zjzc.SmsTest(phoneNo,channelType,status) values(3,1,1);
Query OK, 1 row affected (0.00 sec)

mysql>  insert into zjzc.SmsTest(phoneNo,channelType,status) values(4,1,1);
Query OK, 1 row affected (0.00 sec)

mysql>  insert into zjzc.SmsTest(phoneNo,channelType,status) values(6,1,1);
Query OK, 1 row affected (0.00 sec)

mysql>  insert into zjzc.SmsTest(phoneNo,channelType,status) values(7,1,1);
Query OK, 1 row affected (0.00 sec)

mysql>  insert into zjzc.SmsTest(phoneNo,channelType,status) values(8,1,1);
Query OK, 1 row affected (0.00 sec)

mysql>  insert into zjzc.SmsTest(phoneNo,channelType,status) values(9,1,1);
Query OK, 1 row affected (0.00 sec)

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(13,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


mysql>  insert into zjzc.SmsTest(phoneNo,channelType,status) values(16,1,1); --hang

mysql>  insert into zjzc.SmsTest(phoneNo,channelType,status) values(17,1,1);
Query OK, 1 row affected (0.00 sec)


The locks are normally next-key locks that also block inserts into the “gap” immediately before the record. 


结论: locks 通常是next-key locks  会堵塞 那个record 之前的区间