多列LOCK_DATA引发死锁咨询：'a',NULL,1与'1'锁的差异及死锁原因

一、LOCK_DATA为'a',NULL,1的锁是什么？与LOCK_DATA为'1'的锁的差异 #

• LOCK_DATA为'1'的锁：这是test_table主键索引上的X,REC_NOT_GAP行锁，直接锁定主键值为1的行数据，是InnoDB通过主键条件（如where id=1）进行for update操作时，在主键索引上生成的排他行锁，作用是阻止其他事务修改或锁定该行。
• LOCK_DATA为'a',NULL,1的锁：这是test_table的联合二级索引1_4_index上的X记录锁。因为1_4_index是基于column_1和column_4的联合索引，InnoDB的二级索引叶子节点会额外存储主键值（这里是1）用于回表查询，所以LOCK_DATA会显示为column_1值、column_4值、主键值的组合。这个锁是事务通过二级索引条件（如where column_1='a'）执行for update时，先在二级索引上锁定匹配的条目。

两者核心差异：

• 所属索引层级不同：一个是主键索引（聚簇索引），一个是二级联合索引；
• 锁定对象不同：主键锁锁定的是聚簇索引中的行记录（直接对应实际数据行），二级索引锁锁定的是二级索引中的条目，需要通过主键值回表才能访问实际数据；
• 触发场景不同：主键锁由主键条件的写/锁定操作触发，二级索引锁由二级索引条件的写/锁定操作触发。

二、死锁产生的原因 #

死锁的本质是事务间形成了循环等待锁资源的链条，具体到你的场景：

1. 事务1先执行select ... where id=1 for update，持有test_table主键1的X行锁、test_table2主键1的X行锁，以及两张表的IX表锁；
2. 事务2执行select ... where column_1='a' for update时，InnoDB会先扫描1_4_index，锁定其中匹配的(a,NULL,1)二级索引条目（持有该X锁），随后需要回表获取主键1的行锁，但该锁已被事务1持有，因此事务2进入等待状态；
3. 当事务1执行update test_table set column_1='x' where id=1时，由于column_1是1_4_index的组成字段，InnoDB需要修改该二级索引中(a,NULL,1)的条目，但这个二级索引条目已被事务2锁定，因此事务1开始等待事务2释放该锁；
4. 此时事务1持有主键1的锁，等待事务2的二级索引锁；事务2持有二级索引锁，等待事务1的主键锁，完全满足死锁的四个必要条件（互斥、持有并等待、不可剥夺、循环等待），因此触发死锁。

内容的提问来源于stack exchange，提问作者scanf3