MySQL自增长可能锁表的实验

MySQL技术内幕 Innodb存储引擎 第二版 262页

实验环境

innodb_autoinc_lock_mode设置为0，所有的自增长都会使用AUTO-INC Locking方式。
它是一种特殊的表锁机制,为了提高插入性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。

下面的实验会从反方向验证这一点
如果一个插入SQL没有完成，则该表的其余插入会被阻塞。

数据初始化

drop table if exists test;

create table test
(
    a int primary key auto_increment,
    b int,
    key(b)
)engine=innodb;

insert into test(b) values(1),(4),(7),(10);
commit;

mysql> select * from test;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    4 |
| 3 |    7 |
| 4 |   10 |
+---+------+
4 rows in set (0.00 sec)

首先，开启一个终端Session1 锁定一个范围
然后，开启另一个终端Session2,插入一个被Session1锁定的数据，导致Session2阻塞,
最后，再开启一个终端Session3，会发现因为自增长的锁没有释放(被Session2持有),导致所有范围的插入被阻塞。


Session1锁定一个范围，不能使用悲观锁
如果使用悲观锁如select * from test where b>=7 for update;将直接导致所有插入阻塞。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29254281/viewspace-1079183/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29254281/viewspace-1079183/