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

Read-Uncommitted/Read-Committed隔离级别上锁过程

查询条件	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锁。

Repeatable Read隔离级别上锁过程 

查询条件	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的记录加exclusive next-key lock，对对应的主键行加X锁。因为没有ICP，这里>xx的第一条记录对应的索引行和主键行也会加X锁，同时不会释放。 2:包含索引列 会对索引上<xx的记录加exclusive next-key lock，对对应的主键行和索引加X锁。对>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)。即该语句会锁定范围。

RR和RC隔离级别都要加的锁 

唯一二级索引（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锁。

一个UK更新例子：

CREATE TABLE `t_test` (
  `a` bigint(20) NOT NULL COMMENT 'a',
  `b` varchar(32) NOT NULL COMMENT 'b',
  `c` varchar(32) DEFAULT NULL COMMENT 'c',
  `d` int(11) NOT NULL COMMENT 'd',
  `e` bigint(20) NOT NULL COMMENT 'e',
  PRIMARY KEY (`a`),
  UNIQUE KEY `uk_b_d` (`b`,`d`) USING BTREE,
  KEY `idx_b_e` (`b`,`e`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='test';

insert into t_test values(1,'1','1',1,1);
insert into t_test values(5,'5','5',5,5);
insert into t_test values(10,'10','10',10,10);
insert into t_test values(15,'15','15',15,15);
update t_test set a = 7, b = "5", c = "5",  d = 5, e = 7  where  b = "5" and d = 5;

锁情况：
TRANSACTIONS
======================
Trx id counter 1363
Purge done for trx's n:o < 1363 undo n:o < 0 state: running but idle
History list length 3
Total number of lock structs in row lock hash table 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421893635597552, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 421893635594896, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 1358, ACTIVE 6 sec
3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140418654865152, query id 99 localhost 127.0.0.1 root
TABLE LOCK table `test`.`t_test` trx id 1358 lock mode IX
RECORD LOCKS space id 24 page no 4 n bits 72 index uk_b_d of table `test`.`t_test` trx id 1358 lock_mode X locks rec but not gap
RECORD LOCKS space id 24 page no 3 n bits 72 index PRIMARY of table `test`.`t_test` trx id 1358 lock_mode X locks rec but not gap
======================