#### innodb中各种SQL语句加锁分析 ####

wangfy_

已于 2022-03-16 11:58:24 修改

阅读量228

点赞数 1

分类专栏：数据库文章标签： sql 数据库 database

于 2021-04-08 17:27:27 首次发布

原文链接：https://www.fordba.com/locks-set-by-different-sql-statements-in-innodb.html

版权

数据库专栏收录该内容

86 篇文章 0 订阅

订阅专栏

摘自：MySQL innodb中各种SQL语句加锁分析 | | For DBA

仅做个人备份，浏览请看原文

1.SELECT ... FROM 是一个快照读，通过读取数据库的一个快照，不会加任何锁，除非将隔离级别设置成了 SERIALIZABLE 。在 SERIALIZABLE 隔离级别下，如果索引是非唯一索引，那么将在相应的记录上加上一个共享的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

2. SELECT ... FROM ... LOCK IN SHARE MODE 语句在所有索引扫描范围的索引记录上加上共享的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

3. SELECT ... FROM ... FOR UPDATE 语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。这将堵塞其他会话利用SELECT ... FROM ... LOCK IN SHARE MODE 读取相同的记录，但是快照读将忽略记录上的锁。

4. UPDATE ... WHERE ...语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

当UPDATE 操作修改主键记录的时候，将在相应的二级索引上加上隐式的锁。当进行重复键检测的时候，将会在插入新的二级索引记录之前，在其二级索引上加上一把共享锁。

5. DELETE FROM ... WHERE ... 语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

6. INSERT 语句将在插入的记录上加一把排他锁，这个锁是一个index-record lock，并不是next-key 锁，因此就没有gap 锁，他将不会阻止其他会话在该条记录之前的gap插入记录。

7. INSERT ... ON DUPLICATE KEY UPDATE 和普通的INSERT并不相同。如果碰到重复键值，INSERT ... ON DUPLICATE KEY UPDATE 将在记录上加排他的 next-key锁。

8. REPLACE 在没有碰到重复键值的时候和普通的INSERT是一样的，如果碰到重复键，将在记录上加一个排他的 next-key锁。

9. INSERT INTO T SELECT ... FROM S WHERE ... 语句在插入T表的每条记录上加上 index record lock 。如果隔离级别是 READ COMMITTED, 或者启用了 innodb_locks_unsafe_for_binlog 且事务隔离级别不是SERIALIZABLE，那么innodb将通过快照读取表S(no locks)。否则，innodb将在S的记录上加共享的next-key锁。

CREATE TABLE ... SELECT ... 和 INSERT INTO T SELECT ... FROM S WHERE ... 一样，在S上加共享的next-key锁或者进行快照读取（(no locks）

10. REPLACE INTO t SELECT ... FROM s WHERE ... 和 UPDATE t ... WHERE col IN (SELECT ... FROM s ...) 中的select 部分将在表s上加共享的next-key锁。

11. 当碰到有自增列的表的时候，innodb在自增列的索引最后面加上一个排他锁，叫AUTO-INC table lock 。AUTO-INC table lock会在语句执行完成后进行释放，而不是事务结束。如果AUTO-INC table lock被一个会话占有，那么其他会话将无法在该表中插入数据。innodb可以预先获取sql需要多少自增的大小，而不需要去申请锁，更多设置请参考参数innodb_autoinc_lock_mode.

12.如果一张表的外键约束被启用了，任何在该表上的插入、更新、删除都将需要加共享的 record-level locks来检查是否满足约束。如果约束检查失败，innodb也会加上共享的 record-level locks。

13. lock tables 是用来加表级锁，但是是MySQL的server层来加这把锁的。当innodb_table_locks = 1 (the default) 以及 autocommit = 0的时候，innodb能够感知表锁，同时server层了解到innodb已经加了row-level locks。否则，innodb将无法自动检测到死锁，同时server无法确定是否有行级锁，导致当其他会话占用行级锁的时候还能获得表锁。