（五）MySQL InnoDB中各类语句加锁方式

Dear 丶 2022-08-09 14:39 128阅读 0赞

MySQL InnoDB中各类语句加锁方式

（一）MySQL InnoDB事务模型

（二）MySQL InnoDB锁模型

（三）MySQL InnoDB非锁定一致性读与锁定读

（四）MySQL InnoDB锁类型及幻象读问题

（五）MySQL InnoDB中各类语句加锁方式

（六）事务的提交与回滚极死锁检测、处理和预防

锁定读、UPDATE、DELETE通常在处理SQL语句的过程中在扫描到的每个索引记录上加锁，不关心WHERE条件中可能排除行的非索引条件。比如，A表有两列i和j，i列有索引，j列没索引，当前存在(1,1)，（1,2），（1,3），（1,4），（2,1），（2,2），（2,3），（2,4）……等记录，语句SELECT \* FROM A WHERE i=1 AND j=3;会在所有i=1的索引记录上加锁，而不考虑j=3这个条件。如果查询中使用了辅助索引，InnoDB除了给扫描到的辅助索引加锁，还会查找到对应的聚集索引并在其上加锁。若语句用不到合适的索引，则MySQL会扫描整个表，每个表行都会被加锁，会阻塞其他用户的插入操作。

InnoDB对不同的SQL语句加不同的锁：

SELECT...FROM读数据库快照，不对记录加锁，除非使用的是SERIALLIZABE隔离级别，此时对索引记录加S Next-key Lock。

SELECT...FROM...IN SHARE MODE加S Next-key Lock。

SELECT...FROM...FOR UPDATR /  UPDATE ... WHERE ...  / DELETE FROM ... WHERE ... /加X Next-key Lock。

INSERT在插入的索引记录上加X锁，不会阻止其他事物在插入的记录前的“间隙”插入新的记录。插入记录前，会设置一把 insertion intention gap lock用以表明：不同的事务可以向同一索引“间隙”插入记录而无需相互等待，只要其插入的位置不同。一个事务中insert语句会在插入的行的索引记录上设置一把排它锁。如果有键重复的错误发生，则会在重复的索引记录上设置一把共享锁。在多个session同时插入同一行，且另外的某个session已经持有了该索引记录的排它锁时，共享锁的使用可能导致死锁的出现。

举个例子：

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

Session 1:

START TRANSACTION;

INSERT INTO t1 VALUES(1);

Session 2:

START TRANSACTION;

INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;

INSERT INTO t1 VALUES(1);

Session 1:

ROLLBACK;

三个session：session1会获取行上的X锁，且一直持有到事务结束（提交或者回滚）。session2和session3的操作会引起键重复的错误，因此会请求索引记录上的S锁。因为S锁和X锁不兼容，这两个锁请求会阻塞直到session1中事务结束。当session1回滚后，释放了持有的X锁，队列中的两个S锁请求会同时成功。此时session2与session便会发生死锁。因为两个session中的事务都需要在插入的行上加X锁，而此时因其他session所持有的S锁，谁也获取不到这个X锁。于是会回滚代价较小的事务解除死锁。如果session是提交而非回滚，则提交后成功插入该行，事务结束、释放X锁。session2.session3中的插入因键重复而发生错误语句回滚，持有的S锁被释放。

还有类似的情形，比如t1表中已经存在某行记录。同时session1中某个事务对改行进行删除操作。session2、session3中对改行进行插入操作。session1的事务中会在改行对应的索引记录上添加X锁。session2、session3中的事务因键冲突会申请索引记录上的S锁。sesession1中的事务提交后，记录被删除、X锁释放。session2和session3中的事务获得S锁。因为此时表中没有对应的记录所以session2和session3中的事务认为可以插入。但此时会发生死锁，因为各自在申请插入记录上的X锁时相互等待对方的X锁。

INSERT ... ON DUPLICATE KEY UPDATE 与普通INSERT语句不同，在发生键重复的错误后，在记录上加X Next-key Lock而非 S锁。

REPLACE语句，无唯一键冲突时同INSERT，否则在记录上加X Next-key Lock。

INSERT INTO T SELECT ... FROM S WHERE ...对插入在T表的记录加X锁。若隔离级别为READ COMMITTED或者打开了innodb\_locks\_unsafe\_for\_binlog且隔离级别不为 SERIALIZABLE则不对从S中搜索到的索引记录加锁，否则添加S Next-key Lock。

CREATE TABLE ... SELECT ... ， REPLACE INTO t SELECT ... FROM s WHERE ...， UPDATE t ... WHERE col IN (SELECT ... FROM s ...)与INSERT INTO T SELECT ... FROM S WHERE ...类似。

自增长与锁： InnoDB初始化自增列时会在自增列相关索引的最后一条索引记录加锁。这里使用一种特殊的锁叫AUTO-INC table lock。该锁会在当前语句就结束后释放而非整个事务结束后释放，在有事务持有AUTO-INC table lock时其他事物session不能插入记录。

外键与锁：若表上定义有外键约束，任何需要检查约束条件的insert、update、delete操作均会在待检查记录上添加S锁。