MySQL 各种SQL语句加锁分析

概要

本文源于同事王航威的翻译，对原文做了简单的修改 ，原文地址: http://www.fordba.com/locks-set-by-different-sql-statements-in-innodb.html

Locking read（ SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE），UPDATE以及DELETE语句通常会对通过索引扫描的记录加上 next-key locks ，忽略没有用到索引的那部分where语句。

举个例子：

CREATE TABLE `test` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

select * from test where id > 3 and name <'A' for update;

这条SQL语句的将所有id>3的记录进行加锁，而不是对范围 id>3 and name <'A' 进行加锁，因为name上面没有索引。

如果一个SQL通过二级索引访问表记录，并且在二级索引上设置了一个锁，那么innodb将会在对应的聚簇索引(主键)记录上也加上一把锁。

如果一个SQL语句无法通过索引进行Locking read，UPDATE，DELETE，那么MySQL将扫描整个表，表中的每一行都将被锁定（在RC级别，通过semi-consistent read，能够提前释放不符合条件的记录，在RR级别，需要设置innodb_locks_unsafe_for_binlog为1，才能打开semi-consistent read）。在某些场景下，锁也不会立即被释放。例如一个union查询，生成了一张临时表，导致临时表的行记录和原始表的行记录丢失了联系，只能等待查询执行结束才能释放。

SQL分析

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锁。如果是唯一索引，只需要在相应记录上加行锁record lock。 当UPDATE 操作修改主键记录的时候，将在相应的二级索引上加上隐式的锁。当进行重复键检测的时候，将会在插入新的二级索引记录之前，在其二级索引上加上一把共享锁。
5. DELETE FROM ... WHERE ... 语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加行锁record lock。
6. INSERT 语句会对插入成功的记录上加排他锁，这个锁是一个行锁 index-record lock，并不是next-key 锁，因此就没有gap 锁，它将不会阻止其他会话在该条记录之前的gap插入记录。 在插入记录之前，将会加上一种叫 insert intention gap 的 gap 锁。这个 insert intention gap表示它有意向在这个index gap插入记录，如果其他会话在这个index gap中插入的位置不相同，那么将不需要等待。假设存在索引记录4和7，会话A要插入记录5，会话B要插入记录6，每个会话在插入记录之前都需要锁定4和7之间gap，但是他们彼此不会互相堵塞，因为插入的位置不相同。 如果出现唯一键冲突，将在重复键上加一个共享锁。如果会话1插入一条记录，没有提交，他会在该记录上加上排他锁，会话2和会话3都尝试插入该重复记录，那么他们都会被堵塞，会话2和会话3将尝试在该记录上申请加一个共享锁。如果此时会话1回滚，将发生死锁。 参考死锁案例: 死锁案例之三 死锁案例之一
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无法确定是否有行级锁，导致当其他会话占用行级锁的时候还能获得表锁。

参考资料：

http://docs.fordba.com/mysql/refman-5.6-en/innodb-storage-engine.html#innodb-locks-set