?INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and
does not prevent other sessions from inserting into the gap before the inserted row
INSERTS 设置一个排他锁在插入的记录,锁是一个Index-record lock, 不是一个next-key lock （也就是说,没有一个gap锁）
不会阻止其他会话插入到之前插入值之前的区间
mysql> show create table SmsTest\G;
*************************** 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='短信发送成功记录表'
1 row in set (0.00 sec)
ERROR:
No query specified
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> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into SmsTest values(15,15,1,1);
Query OK, 1 row affected (0.00 sec)
Session 2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from SmsTest where sn=8;
Query OK, 1 row affected (0.00 sec)
mysql> delete from SmsTest where sn=9;
Query OK, 1 row affected (0.00 sec)
mysql> delete from SmsTest where sn=10;
Query OK, 1 row affected (0.00 sec)
mysql> delete from SmsTest where sn=11;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from SmsTest where sn=12;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from SmsTest where sn=13;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from SmsTest where sn=14;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from SmsTest where sn=15; --hang
