一个innodb next-key lock的问题
表结构:
CREATE TABLE `test_lock` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=utf8
隔离级别:REPEATABLE-READ
innodb_locks_unsafe_for_binlog:0
表内容:
+----+
| id |
+----+
| 12 |
| 39 |
| 59 |
+----+
session1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_lock where id <= 39 for update;
+----+
| id |
+----+
| 12 |
| 39 |
+----+
2 rows in set (0.00 sec)
session2:
mysql> insert into test_lock values(40);
问题:
1.
id
是主键,为什么session2还会等待session1的锁呢
2.就算有next-key lock,它不应该是锁住(39, 59]吗,为什么在session2中插入的值是70时还会等待呢。
卖萌so可耻
11 years, 6 months ago