innodb不同场景下加锁分析总结

查询条件

SQL示例

加锁分析

查询条件为主键等值

SELECT … WHERE PK = XX FOR UPDATE;

1：如果有对应记录则对记录加LOCK_X|LOCK_REC_NOT_GAP锁

2：如果没有则不加锁，以下场景都相同处理

UPDATE … WHERE PK = XX;

1：如果未更新其他索引列。

只需要在主键记录上加X锁即可，加锁为LOCK_X|LOCK_REC_NOT_GAP。

2：更新包括其他索引列。

除了主键记录加X锁，还需要在涉及索引上加LOCK_X|LOCK_REC_NOT_GAP锁。

DELETE … WHERE PK = XX;

对主键、各个索引对应的记录都要加X锁，LOCK_X|LOCK_REC_NOT_GAP。

查询条件为主键范围

select * from t where c1 >= 20 for update;

会对所有满足条件的记录加锁，如果没有则不加锁

update t set c2 = c2 + 1 where c1 >= 20;

会依次对c1 >=20的主键记录行加X锁，对应的索引行做加X锁操作。

查询条件为唯一索引等值

SELECT … WHERE UK = XX FOR UPDATE;

需要在索引记录上加X锁:LOCK_X|LOCK_REC_NOT_GAP，同时还要在对应主键行上加X锁。

UPDATE … WHERE UK = XX;

1:未更新其他索引列

对唯一索引上数据加X锁（LOCK_X|LOCK_REC_NOT_GAP），然后对应的主键行也需要加X锁。

2:更新其他索引列

依次对唯一索引数据、主键行、索引数据加X锁。

DELETE … WHERE UK = XX;

会对唯一索引数据加X锁，根据唯一索引找到主键行后，会再依次对主键行、唯一索引、索引数据加X锁。

查询条件为唯一索引范围

SELECT … WHERE UK >= XX FOR UPDATE;

1: 如果是通过主键，则对主键行依次加X锁， 同时在加锁后，分析发现不满足条件会立即释放该行上的X锁。

2：如果是通过唯一索引，则依次对满足条件的唯一索引、主键记录加X锁。

UPDATE … WHERE UK <= XX FOR UPDATE;

会对唯一索引和对应主键行依次加X锁，然后判断不满足range条件，释放唯一索引和对应主键行上的X锁。

查询条件为非唯一索引

SELECT … WHERE IDX = XX FOR UPDATE;

非唯一索引情况与唯一索引情况加锁情况一致

查询条件上无索引

SELECT … WHERE COL = XX FOR UPDATE;

路径选择主键，会依次对主键行加锁，分析条件，不满足条件释放锁，满足条件持有锁。

多条件查询

select * from t where c2 = xx and c3 = xx for update;

当存在多个条件的时候，除了主键行上的锁，其他的加锁情况取决于选择的路径。

选择了走唯一索引，就会对满足条件的唯一索引行加X锁，然后对主键行加X锁。

主键路径，就会对所有行一次加X锁，分析条件，最终持有主键上c1 = 20的X锁。

查询条件

SQL示例

加锁分析

查询条件为主键等值

SELECT … WHERE PK = XX FOR UPDATE;

1：如果有对应记录则对记录加LOCK_X|LOCK_REC_NOT_GAP锁

2：如果没有则会对后面的记录加GAP锁，（LOCK_X|LOCK_GAP），防止有其他语句插入pk=xx的行。

UPDATE … WHERE PK = XX;

1：如果未更新其他索引列。

只需要在主键记录上加X锁即可，加锁为LOCK_X|LOCK_REC_NOT_GAP。

2：更新包括其他索引列。

除了主键记录加X锁，还需要在涉及索引上加LOCK_X|LOCK_REC_NOT_GAP锁。

3：没有满足条件的记录则不加锁

DELETE … WHERE PK = XX;

1：如果有满足条件的记录对主键、各个索引对应的记录都要加X锁，LOCK_X|LOCK_REC_NOT_GAP。

2：没有则不加锁

查询条件为主键范围

select * from t where c1 >= 20 for update;

这里会对c1=20加X锁(LOCK_X|LOCK_REC_NOT_GAP)，对c1>20对应的行加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，同时会对表示记录上界的’supremum’加exclusive next-key lock。以阻塞其他事务对c1>=20的插入。

select * from t where c1 <= 20 for update;

这里会依次对c1<=20的主键记录加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)锁。且在判断c1=30不符合查询条件后，虽然server层调用unlock_row，但对于RC隔离级别以上且没有设置innodb_locks_unsafe_for_binlog那么并不会释放锁。

update t set c2 = c2 + 1 where c1 >= 20;

1:未更新其他索引列。

加锁与上面SELECT… WHERE PK >= XX FOR UPDATE;一致。

2:更新包含索引列。

对主键c1=20加X锁，索引行加X锁，然后对后面的主键行加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，同时对应的i_c2索引行加X锁，最后对表示记录上界的’supremum’加exclusive next-key lock。

UPDATE … WHERE PK <= 20;

1:未更新其他索引列。

加锁与SELECT… WHERE PK <= XX FOR UPDATE;一致

2:包含索引列。

对pk<=20的主键满足条件记录加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，同时对应的索引行加X锁。然后对pk>20的第一条记录加exclusive next-key lock，因不满足条件，因此server层查询停止。同样并不会释放后面记录上的锁。

DELETE … WHERE PK >= 20;

会对pk=20加X锁，对pk=20对应的索引加X锁，然后依次对pk>20的主键记录加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，同时对应的索引行加X锁，最后对’supremum’加LOCK_X|LOCK_ORDINARY。

查询条件为唯一索引等值

SELECT … WHERE UK = XX FOR UPDATE;

需要在索引记录上加X锁:LOCK_X|LOCK_REC_NOT_GAP，同时还要在对应主键行上加X锁。

UPDATE … WHERE UK = XX;

1:未更新其他索引列

对唯一索引上数据加X锁（LOCK_X|LOCK_REC_NOT_GAP），然后对应的主键行也需要加X锁。

2:更新其他索引列

依次对唯一索引数据、主键行、索引数据加X锁。

DELETE … WHERE UK = XX;

会对唯一索引数据加X锁，根据唯一索引找到主键行后，会再依次对主键行、唯一索引、索引数据加X锁。

查询条件为唯一索引范围

SELECT … WHERE UK >= XX FOR UPDATE;

1: 如果是通过主键，对满足条件的主键记录分别加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，同时对上界’supremum’加exclusive next-key lock，锁住全部数据范围。

2：如果是通过唯一索引，那么会对满足条件的索引记录分别加exclusive next-key lock，对应主键行加X锁，同时对索引上的’supremum’ record加exclusive next-key lock。

SELECT … WHERE UN <= XX FOR UPDATE;

对索引上满足条件的记录加exclusive next-key lock，对对应的主键行加X锁，然后对大于xx的第一条记录加exclusive next-key lock，且并不会去释放。

UPDATE … WHERE UK >= XX;

1:未包含索引列。

等同上面指定走唯一索引的SELECT…FOR UPDATE语句加锁。

2: 包含索引列。除了之前提到的主键和唯一索引加锁之外，对应被涉及到的索引行也要加X锁。

UPDATE … WHERE UK <= XX FOR UPDATE;

1:未包含索引列。

会对索引上xx的第一条记录对应的索引行和主键行也会加X锁，同时不会释放。

2:包含索引列

会对索引上xx的第一条记录加exclusive next-key lock，对应主键行加X锁。

DELETE … WHERE UK >= XX;

走索引，会对>=xx的第一条记录加exclusive next-key lock，对应主键行加X锁，索引上数据行进行加X锁操作，对索引上’supremum’ record加exclusive next-key lock。

查询条件为非唯一索引等值

SELECT … WHERE IDX = XX FOR UPDATE;

会对=xx在索引上加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，对应主键加X锁(LOCK_X|LOCK_REC_NOT_GAP)，然后在下一条记录上加exclusive gap lock(LOCK_X|LOCK_GAP)。即该语句会锁定范围。

唯一二级索引（uk)更新或插入

UPDATE set uk=xxx… WHERE UK = XX;

insert into ...uk =xxx

如果UK原来存在，需要做去重判断，此时要对这个UK加S锁。比方说插入uk=1,表里已经有这个uk=1的记录，或者被删除还未被清理，需要先对uk=1这索引加S LOCK_ORDINARY锁再加X锁。更新UK，这等价于先删除再插入，一定有一条相同UK老记录，也需要先加S锁。

原因在于代码逻辑中 row_ins_scan_sec_index_for_duplicate 重复检查要加S LOCK_ORDINARY锁。