InnoDB中不同SQL语句设置锁的情况总结

在Mysql中，锁定读、更新、删除操作通常会对SQL语句处理过程中扫描到的每条索引记录设置记录锁。语句中是否存在排除该行的WHERE条件并不重要。InnoDB不记得确切的WHERE条件，只知道扫描了哪些索引范围。next-key锁通常会阻塞往记录之前的间隙插入数据。可以显示禁用间隙锁，使得next-key锁不可用。事务的隔离级别也会影响锁的设置。

如果在搜索中使用了二级索引，并且在二级索引上设置的是排他锁，InnoDB还会检索相应的聚集索引，并在聚集索引上也设置锁。如果在SQL语句中没有使用合适的索引，此时MySQL必须扫描整个表来处理该语句，表中的每一行都会被锁住，这将导致其他用户往该表的插入操作全部阻塞。所以，给表创建合适的索引非常重要，可以在查询时不扫描多余的行。

InnoDB 设置特定类型的锁如下

SELECT ... FROM：是一致性读取，该语句读取的是数据库快照并且不设置锁，除非事务的隔离级别设置为SERIALIZABLE。在SERIALIZABLE隔离级别下执行该语句会在满足条件的索引记录上设置共享next-key锁。但是，在使用唯一索引查找唯一行的语句时，只需要索引记录锁。

SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE：扫描行时为行加锁，对于不满足条件的行会释放锁（例如，不满足WHERE字句中给出的条件）。但是在某些情况下，可能不会立即释放锁，因为在查询期间，结果行与原始源之间的关系会丢失。例如，在UNION查询中，可能会先将表中扫描和锁定的行插入到临时表中，然后在评估它们是否符合结果集。在这种情况下，临时表中的行与原始表中的行的关系丢失，所以要在查询执行结束后才能解锁原始表中的行。

SELECT ... LOCK IN SHARE MODE：给查询过程中遇到的所有索引记录设置共享的next-key锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要使用记录锁。

SELECT ... FOR UPDATE：给查询过程中遇到的每条记录设置排他的next-key 锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要使用记录锁。对于查询过程中遇到的索引记录，SELECT ... FOR UPDATE会阻塞其他会话执行SELECT ... LOCK IN SHARE MODE或在某个事务隔离级别下读取数据。一致性读将忽略存在于读取视图中的记录上的任何锁。

UPDATE ... WHERE ... ：在搜索过程中遇到的每条记录上设置排他的next-key锁。如果WHERE子句中使用了唯一索引，则将使用记录锁。如果UPDATE的是聚集索引，则与其相关的二级索引也会被隐式上锁。当插入新的二级索引记录之前执行重复性检查扫描和正在插入新的二级索引记录时，UPDATE操作会对受影响的二级索引记录设置共享锁。

DELETE FROM ... WHERE ...：对搜索遇到的每条记录上设置排他的next-key锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要使用记录锁。

INSERT：对插入的行设置排他锁。这个锁是记录锁，而不是next-key锁（也就是说，你没有间隙锁），并且不会阻塞其他会话往插入行之前的间隙插入记录。插入语句在执行插入前会先获得插入意向锁。插入意向锁表示插入的意图，多个事务往相同间隙中的不同位置插入时则他们无需相互等待。假设有值为4和7的索引记录。若两个单独事务分别插入5和6，在获得插入行的排它锁之前会分别获得4和7之间间隙的插入意向锁。因为这两个事务插入的行不同，所以不会阻塞。

如果出现重复键错误，则对重复索引记录加共享锁。此时，如果有一个事务已经获得了该行的排他锁，此时，有多个事务试图插入同一行， 那么共享锁的使用可能会导致死锁。如果有一个事务要删除该行，此时，有多个事务试图插入同一行，也有可能发生这种情况。

假设InnoDB中有如下结构表t1:

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

假设有三个事务按顺序执行以下操作：

Session1：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session2：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session3：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session1：

ROLLBACK;

事务1的插入操作获取该行的排他锁。事务2和事务3的操作都会导致重复键错误，他们都请求该行的共享锁。当事务1回滚时，它释放该行上的排他锁，事务2和事务3获得该行的共享锁。此时，事务2和事务3就会出现死锁：由于另一方持有共享锁，双方都不能获得该行的排他锁。

如果表中已经包含键值为1的行，并且三个会话按顺序执行以下操作也会出现类似的情况：

Session1：

START TRANSACTION;
DELETE FROM t1 WHERE i = 1;

Session2：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session3：

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session1：

COMMIT;

事务1的第一个操作获取该行的排他锁。事务2和事务3的操作都会导致重复键错误，此时这两个事务都请求该行的共享锁。当事务1提交时，它释放该行上的排他锁，并且授权事务2和事务3的共享锁请求。此时，事务2和事务3就会出现死锁：由于另一方持有共享锁，双方都不能获得该行的排他锁。

INSERT ... ON DUPLICATE KEY UPDATE：不同于简单的INSERT，当重复键错误发生时，请求的是该行的排他锁而不是共享锁。如果是主键索引的值重复，则采用排他的记录锁；如果是唯一索引的值重复，则采用next-key锁。

REPLACE：如果唯一键中没有该值，和REPLACE操作和INSERT操作一样。否则，需先获得该行排他的next-key锁。

INSERT INTO T SELECT ... FROM S WHERE ...：对插入到T中的每一行设置排他的记录锁（没有间隙锁）。如果事务的隔离级别是读已提交，或者启用innodb_locks_unsafe_for_binlog并且事务的隔离级别不是SERIALZABLE，InnoDB会将S作为一致性读进行搜索（没有锁）。否则，InnoDB会对来自S的行设置共享的next-key锁。如下情景中，InnoDB必须设置锁：在使用基于语句的二进制日志进行回滚恢复时，每条SQL语句都必须以与原来完全相同的方式进行。

CREATE TABLE ... SELECT ...：执行SELECT语句时使用共享的next-key 锁，或者作为一致性读取，类似于INSERT ... SELECT。当SELECT用于如下语句中时，InnoDB会对满足条件的S表中的行设置共享的next-key锁：REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...)。

当初始化表中AUTO_INCREMENT列时，InnoDB会在与AUTO_INCREMENT关联的索引的最后上设置排他锁。在innodb_autoinc_lock_mode=0的情况下，当访问auto-increment计数器时，InnoDB使用特殊的AUTO-INC表锁模式，在该模式下当前 SQL 语句的末尾获取并持有锁（而不是整个事务的结尾持有锁）。当某一事务持有AUTO-INC表锁时，将阻塞其它事务往表中插入的操作。当innodb_autoinc_lock_mode=1时，批量插入也会出现这种情况。当innodb_autoinc_lock_mode=2时，不能使用表级锁AUTO-INC。当InnoDB获取已初始化的AUTO_INCREMENT列的值时无需设置任何锁。

如果在一个表上定义了一个外键约束，任何需要检查约束条件的操作，如：插入，更新，删除，都会对其检查约束的过程中查看的记录上设置共享的记录锁。InnoDB也会在约束失败的情况下设置这些锁。

LOCK TABLES：设置表锁，该锁是由InnoDB层之上的MySQL层设置的。如果innodb_table_locks = 1(默认值)和autocommit = 0,InnoDB能够识别表锁，InnoDB层之上的MySQL层能够识别行级锁。否则，InnoDB 的自动死锁检测无法检测到涉及表锁的死锁。此外，因为在这种情况下，更高的MySQL层识别不了行级锁，所以，在另一个事务当前持某表的行级锁的情况下获得该表的表锁是有可能的。这并不会破坏事务的完整性。

LOCK TABLES：如果innodb_table_locks=1（默认值），那么使用LOCK TABLE锁表时，每个表上都会有两个表锁。除了MySQL层上的表锁，还需要InnoDB层的表锁。可以通过设置innodb_table_locks=0，避免获取InnoDB层的表锁。如果没有获得InnoDB层的表锁，即使表中的一些记录正被其他事务上锁，LOCK TABLES操作依然可以完成。

在MySQL5.7中，如果innodb_table_locks=0，那么当使用 LOCK TABLES ... WRITE给表显示加锁将不会起作用，当读操作或写操作时使用 LOCK TABLES ... WRITE给表隐式加锁时（例如，通过触发器）或使用LOCK TABLES ... READ时将会起作用。

当事务提交或终止时，该事物将释放其持有的所有InnoDB锁。因此，在autocommit = 1的情况下使用LOCK TABLES没有任何意义，因为获得的InnoDB表锁会立即被释放。

不能在事务执行中锁定其他表，因为LOCK TABLES会隐式地执行COMMIT和UNLOCK TABLES从而释放表锁。

参考：Locks Set by Different SQL Statements in InnoDB