Freedom

InnoDB中不同SQL语句设置的锁
- InnoDB为各种SQL语句设置的锁介绍如下:

InnoDB中不同SQL语句设置的锁

参考：15.7.3 Locks Set by Different SQL Statements in InnoDB

锁定读(locking read), UPDATE 和 DELETE 语句, 通常会在扫描到的索引记录(index record)上设置记录锁(record locks)。不管SQL语句中是否包含WHERE条件都会上锁。因为InnoDB并不会记录确切的WHERE条件, 只知道自己扫描了哪些索引范围。一般使用临键锁(next-key locks), 这样就可以阻塞(block)其他事务将新行插入到前面的间隙(gap)中。当然, 可以显式地禁用间隙锁(gap locking), 那也就不会使用临键锁。

如果搜索中用到了二级索引且该索引记录锁为排他锁, InnoDB还会检索相应的聚集索引记录(clustered index record)并对其上锁。

如果没找到索引, 那么MySQL会进行全表扫描(scan the entire table), 表中的每一行都将被锁定, 从而阻塞其他会话对表的所有插入。所以创建良好的索引非常重要, 执行查询时就不需要去扫描很多不必要的行。

InnoDB为各种SQL语句设置的锁介绍如下:

SELECT ... FROM, 一致性读, 读取的是快照, 一般不上锁; 只有事务隔离级别是 SERIALIZABLE 才会上锁。对于 SERIALIZABLE 级别, 会在搜索到的索引记录上设置共享的临键锁(next-key locks)。但是, 对于使用唯一索引来查询唯一行的SQL语句, 则只需要设置一个索引记录锁。
对于 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE, 会对扫描到的行上锁, 但不在结果集中的行一般会立即释放(比如不符合 WHERE 子句中的过滤条件)。但某些情况下行锁可能不会立即释放, 因为在查询执行期间, 可能会丢失结果行与其原始数据源之间的联系。例如, 在 UNION 语句中, 表中被扫描到(并锁定)的行在计算是否符合结果集之前, 可能会被插入到临时表中。在这种情况下, 临时表中的行与原始表中的行之间的关系会丢失, 所以要等查询执行完之后行锁才会被释放。
SELECT ... LOCK IN SHARE MODE 在搜索遇到的索引记录上设置共享临键锁（shared next-key locks）。但如果是通过唯一索引来检索唯一行, 则只需要锁定单个索引记录。
SELECT ... FOR UPDATE 在搜索到的每条记录上设置排他临键锁。例外是通过唯一索引来搜索唯一行的语句, 仅需要锁定一条索引记录。

对于搜索遇到的索引记录, SELECT ... FOR UPDATE 会阻塞其他会话执行 SELECT ... LOCK IN SHARE MODE, 以及阻塞某些隔离级别的事务读取数据。一致性读将忽略 read view 中记录上设置的任何锁。
UPDATE ... WHERE ... 在搜索到的每条记录上设置排他临键锁（exclusive next-key lock）。例外是通过唯一索引来搜索唯一行的语句, 仅需要锁定一条索引记录。
当 UPDATE 修改聚集索引记录时, 将对受影响的二级索引记录进行隐式锁定。在插入新的二级索引记录前执行重复项检查时, 以及在插入新的二级索引记录时, UPDATE 操作还会对受影响的二级索引记录设置共享锁。
DELETE FROM ... WHERE ... 在搜索到的每条记录上都设置排他临键锁。例外是通过唯一索引来搜索唯一行的语句, 仅需要锁定一条索引记录。
INSERT 对插入的行设置排他锁。是索引记录锁（index-record lock）, 而不是临键锁(即没有间隙锁), 不会阻止其他会话插入新行到前面的间隙中。
- 在插入新行前, 会设置一个插入意向间隙锁(insert intention gap lock)。发出插入意向的信号, 如果多个事务想要在同一个索引间隙中插入新记录, 只要不是同一个槽位, 则无需等待。假设索引记录的值分别为4和7。有两个事务如果分别想要插入5和6这两个值, 在获得排它锁之前, 每个事务都会先设置插入意向锁来锁定4到7之间的间隙, 但是彼此之间没有阻塞, 因为行没有冲突。
- 如果出现重复键错误(duplicate-key error), 则会在重复索引记录上设置一个共享锁。如果另一个会话已经获取到排它锁, 并且有多个会话想要插入同一行的话, 则这个共享锁可能会导致死锁。加入另一个会话删除了该行, 则会发生这种情况。例如InnoDB表t1具有以下结构:
```
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;
```
会话1的第一个操作获取该行的排他锁。会话2和会话3的操作都会产生重复键错误, 并请求该行的共享锁。当会话1回滚时, 会释放该行的排他锁, 而会话2和会话3排队等待共享锁的请求会被授予。这时候, 会话2和会话3就会发生死锁: 由于对方持有了共享锁, 所以两个会话都无法得到该行的排他锁。

如果表中包含了键值为1的行, 并且三个会话按以下顺序执行, 也会发生类似的情况:

Session 1:
```
START TRANSACTION;
DELETE FROM t1 WHERE i = 1;
```
Session 2:
```
START TRANSACTION;
INSERT INTO t1 VALUES(1);
```
Session 3:
```
START TRANSACTION;
INSERT INTO t1 VALUES(1);
```
Session 1:
```
COMMIT;
```
会话1的第一个操作获取该行的排他锁。会话2和会话3的操作都会导引起重复键错误, 然后会请求该行的共享锁。会话1提交后, 释放该行的排他锁, 并授予会话2和会话3排队请求的共享锁。这时候, 会话2和会话3就会发生死锁: 由于对方持有了共享锁, 所以两个会话都无法得到该行的排他锁。
INSERT ... ON DUPLICATE KEY UPDATE 和简单的 INSERT 语句不同, 在发生重复键错误时, 会在要更新的行上设置排他锁, 而不是共享锁。对重复的主键值(primary key value)采用排他索引记录锁。对重复的唯一键值(unique key value)设置排他临键锁。
如果唯一键上没有冲突, 则 REPLACE 的处理方式和 INSERT 一样。如果有冲突, 则会在要替换的行上设置排他临键锁。
INSERT INTO T SELECT ... FROM S WHERE ... 在插入T的每一行上设置排他记录锁(不带间隙锁)。如果事务隔离级别为READ COMMITTED, 或者事务隔离级别不是 SERIALIZABLE但启用了 innodb_locks_unsafe_for_binlog, 则InnoDB会对S表进行搜索, 以使其保持一致性读(无锁)。其他情况下, InnoDB 会在 S 的行上设置共享临键锁, 为什么必须设置锁呢? 原因是使用基于语句的 bin-log 进行前滚恢复时, 必须以和原始操作完全相同的方式来执行每个SQL语句。

CREATE TABLE ... SELECT ... 使用共享临键锁, 或使用一致性读来执行SELECT, 类似于 INSERT ... SELECT。

使用 SELECT 来构造 REPLACE INTO t SELECT ... FROM s WHERE ... 或者 UPDATE t ... WHERE col IN (SELECT ... FROM s ...)时, InnoDB在 s 表的行上设置共享临键锁。
在指定了 AUTO_INCREMENT 属性列的表上初始化数据时, InnoDB 会在关联的索引末尾设置排他锁。

在 innodb_autoinc_lock_mode=0 的情况下, InnoDB 使用一种特殊的表锁定模式 AUTO-INC, 在这种模式下, 访问 auto-increment 计数器时, 需要获取锁并保持到当前SQL语句结束(不是整个事务)。持有 AUTO-INC 表锁时, 其他客户端无法插入该表。而对于 innodb_autoinc_lock_mode=1 的批量插入, 也会发生相同的行为。表级锁 AUTO-INC 与 innodb_autoinc_lock_mode=2 不能一起使用。

在获取先前初始化的 AUTO_INCREMENT 列值时, InnoDB不设置任何锁。
如果定义了 FOREIGN KEY 约束, 那么所有 insert, update, 以及 delete 都需要检查约束条件, 设置共享记录锁。在约束检查失败的情况下, InnoDB也会设置锁。
LOCK TABLES 设置表锁, 但设置的是比 InnoDB 更高层级的 MySQL 锁。如果是默认值 innodb_table_locks = 1, 并且 autocommit = 0, 则 InnoDB 能感知到表锁, MySQL层级也会感知到行级锁。

否则, InnoDB的自动死锁检测就无法探测到涉及这类表锁的死锁。同样, 在这种情况下, 上层的MySQL也感知不到行级锁, 可能会对其他会话持有行级锁的表中去设置表锁。但是，这不会影响事务的完整性。
如果是默认的 innodb_table_locks=1, 那么 LOCK TABLES 会在每张表上获取两个锁。除了MySQL层级的表锁外, 还获取一个 InnoDB 表锁。 MySQL 4.1.2 之前的版本不会获取 InnoDB 表锁；老版本的行为可指定 innodb_table_locks=0 来模拟。如果不获取 InnoDB 表锁, 表中的记录如果被其他事务锁定, LOCK TABLES 也会执行成功。

在MySQL 5.7中, innodb_table_locks=0 对于用 LOCK TABLES ... WRITE 显式锁定的表不起作用。但对于读模式的表锁, 以及通过 LOCK TABLES ... READ, LOCK TABLES ... WRITE(比如触发器) 隐式触发的读锁/写锁则起作用。
事务提交或中止时, 会释放其持有的所有 InnoDB 锁。所以在 autocommit=1 模式下, 对InnoDB表执行 LOCK TABLES 并没有什么意义, 因为获取的 InnoDB 表锁立即就会释放掉。
在事务执行过程中无法锁定其他表, 因为 LOCK TABLES 会执行隐式的 COMMIT' 和 UNLOCK TABLES` 。