执行后:
create table testdl (id int auto_increment, c int, primary key (id), key idx_c (c));
begin;
delete from testdl where c = 1000;
begin;
insert into testdl (c) values (?);
select * from performance_schema.data_locks
的输出结果如下:
+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+
| INNODB | 140043377180024:1073:140043381454544 | 2322 | 48 | 218 | test | testdl | NULL | NULL | NULL | 140043381454544 | TABLE | IX | GRANTED | NULL |
| INNODB | 140043377180024:12:5:1:140043381451552 | 2322 | 48 | 218 | test | testdl | NULL | NULL | idx_c | 140043381451552 | RECORD | X,INSERT_INTENTION | WAITING | supremum pseudo-record |
| INNODB | 140043377180872:1073:140043381460688 | 2321 | 49 | 154 | test | testdl | NULL | NULL | NULL | 140043381460688 | TABLE | IX | GRANTED | NULL |
| INNODB | 140043377180872:12:5:1:140043381457776 | 2321 | 49 | 154 | test | testdl | NULL | NULL | idx_c | 140043381457776 | RECORD | X | GRANTED | supremum pseudo-record |
+
事务2正在等待获取插入意图锁(负无穷大,正无穷大),因为事务1持有下一个键锁(负无穷大,正无穷大),所以它无法继续。
但在执行后
create table testdl (id int auto_increment, c int, primary key (id), key idx_c (c));
insert into testdl values(1, 1000);
begin;
select * from testdl where c = 500 for update;
select * from performance_schema.data_locks
的输出结果是:
+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+
| INNODB | 140043377180872:1074:140043381460688 | 2341 | 49 | 167 | test | testdl | NULL | NULL | NULL | 140043381460688 | TABLE | IX | GRANTED | NULL |
| INNODB | 140043377180872:13:5:2:140043381457776 | 2341 | 49 | 167 | test | testdl | NULL | NULL | idx_c | 140043381457776 | RECORD | X,GAP | GRANTED | 1000, 1 |
+
事务1持有间隙锁 (负无穷,(1000, 1)),因此其他事务无法向该间隙插入数据。
select * from testdl where c = 500 for update
无法匹配记录时,我却无法插入一个c = 100
的记录。毕竟,我选择了使用c = 500 for update
,而不是c < 500
。如果允许插入一个c = 100
的记录,会有什么问题吗?这是因为某些原因吗?或者只是一种实现方式,实际上不会有任何问题发生? - Dean Winchesterselect * from testdl where c > 500 for update
,那么在我完成之前,所有具有c > 500
的并发写入都应该被禁止。但是,如果我执行select * from testdl where c = 500 for update
,如果另一个事务插入一个c = 100
的记录,可能会造成什么样的危害呢? - Dean Winchester