15.7.3 不同的SQL语句加的锁

锁定读，UPDATE，DELETE语句通畅会在语句执行过程中扫描到的每一条索引记录上添加记录锁。不管这条记录是不是不符合语句中的where条件。 InnoDB不记得确切的where条件，只知道要扫描哪些索引区间。添加的锁通畅是next-key锁，这种锁会阻止向记录之前的间隙插入记录。但是，间隙锁可以被显式禁用，禁用后就不会使用next-key锁。想了解更多，可查看章节 15.7.1，”InnoDB Locking“。事务隔离级别也会影响到添加什么样的锁，可以查看章节 15.7.2.1，”Transaction Isolation Levels“。

如果在搜索过程中使用到了二级索引，且添加的索引记录锁是排它锁，InnoDB也会查找相关的聚簇索引记录并在这些记录上添加锁。

如果你的语句在执行时没有合适的索引，导致MySQL必须扫描整张表，表中的每一条记录都会被锁住，最终导致其它用户无法向这张表中插入记录。创建好的索引很重要，这可以使你的查询只会扫描必要的行。

InnoDB会设置特定类型的锁，如下描述。

SELECT … FROM 是一个一致性读，读取的是数据库的一个快照，不会添加锁，除非事务隔离级别是串行化。在串行化级别下，搜索时会在扫描的记录上添加共享next-key锁。然而，如果是使用唯一索引查找一条唯一记录，就只会添加记录锁。
SELECT … FOR UPDATE和SELECT … FOR SHARE语句如果使用了唯一索引，会在扫描到的记录上添加记录锁，并且会释放那些不符合结果集中包含的条件的记录上的锁（例如，不符合where条件）。然而，在某些条件下，记录上的锁可能不会被立即释放，因为结果集记录和数据源之间的关系可能会在查询过程中丢失。例如，在一个UNION查询中，扫描到（并锁定）的记录可能会在评估结果集之前被插入一个临时表。这这种场景下，临时表中的记录和源表质检的关系会丢失，后面的记录上的锁直到查询执行结束才会被释放。
对于锁定读（SELECT with FOR UPDATE 或 FOR SHARE），UPDATE和DELETE语句，添什么样的锁，要看语句是使用唯一索引查找唯一记录还是range类型的搜索条件。
如果是使用唯一条件查询唯一索引，InnoDB只会锁定查找到的索引记录，不会锁住之前的间隙。
如果是其它搜索条件，或是非唯一索引，InnoDB会锁住扫描到的索引区间，使用next-key锁来组织其它会话向区间之中的间隙插入记录。想了解更多关于间隙锁和next-key锁，请查阅章节 15.7.1 “InnoDB Locking”。
UPDATE … WHERE …会在搜索到的每一条记录上添加排它锁。然而，如果是使用唯一索引索索唯一记录，则只会添加索引记录锁。
当UPDATE语句修改了一条聚簇索引记录时，会在相关二级索引记录上添加隐式锁。UPDATE操作在插入新的二级索引记录钱做重复性检查，以及插入新的二级索引记录时，也会在受影响的二级索引记录上添加共享锁。
DELETE FROM … WHERE … 会在搜索到的每一条记录上添加排它next-key锁。但是，如果是使用唯一索引查找唯一记录，则只会获取记录锁。
INSERT会在插入的记录上添加排它锁。这个锁是一个记录锁，不是next-key锁（意思是，没有间隙锁），不会阻止其它会话插入之前的间隙。在插入记录之前，会添加一种称为插入意向间隙锁的间隙锁。这种锁意味着插入时多个要插入这个间隙的事务不必互相等待，除非它们要插入同样的位置。假设有索引记录，其值分别为4和7.有两个不同的事务要插入值分别为5和6的记录，不会相互阻塞，因为不冲突。如果发生了duplicate-key错误，会在重复的记录上添加共享锁。这里共享锁的使用可能会导致死锁，如果有一个事务已经获取到了排它锁，而另外有多个不同的会话要插入同样的记录的话。如果这个会话删除了这条记录，就会发生死锁。现在假设有一张表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;

session1的第一个操作获取了这条记录的排它锁。session2和session3的操作会导致duplicate-key错误，这两个会话都会要求获取这条记录上的共享锁。当session1回滚时，会释放这条记录的排它锁，session2和session3都会获取到这条记录上的共享锁：哪一个都不能获取到排它锁，因为每一个都持有共享锁。

如果表中已经存在一条key值为1的记录而三个会话进行如下操作的话，也会发生相似的情况： session1:

START TRANSACTION;
DELETE FROM T1 WHERE id=1;

session2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

session3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

session1:

COMMIT;

session1的第一个操作获取了记录的排它锁。session2和session3的操作都会导致duplicate-key错误，都会要求获取这条记录上的共享锁。当session1提交时，会释放这条记录的排它锁，并赋予session2和session3其要求的共享锁。这时候，session2和session3死锁了：它们都不能获得这条记录的排它锁，因为它们同时持有这条记录的共享锁。

INSERT … ON DUPLICATE KEY UPDATE 和普通INSERT语句不通，当发生duplicate-key错误时，会在重复的记录上添加排它锁，而不非共享锁。如果是主键索引重复，会添加排它记录锁。如果是唯一索引重复，会添加排它next-key锁。
REPLACE。如果没有发生唯一索引冲突，REPLACE和INSERT一样。否则，就会在被replace的记录上添加排它next-key锁。
INSERT INTO T SELECT … FROM S WHERE …语句。这种语句会在要插入表T的每一条记录上都添加排它的索引记录锁（没有间隙锁）。如果事务隔离级别是读已提交，InnoDB在表S上使用一致性读的方式搜索（没有锁）。否则，InnoDB会在从表S中读取到的记录上添加共享的next-key 锁。在下面的场景中，InnoDB必须加锁：在使用基于语句的binlog进行前置回滚操作时，每一条sql语句的执行方式都必须和原语句的执行方式一样。

CREATE TABLE … SELECT …执行SELECT时，使用共享的next-key锁，或着是一致性读，像INSERT … SELECT那样。

当在REPLACE INTO t SELECT … FROM A WHERE … 或 UPDATE T … WHERE col1 IN (SELECT … FROM s …)使用SELECT时， InnoDB在从s查到的每条记录上添加next-key 锁。

在初始化自增长列时，InnoDB会在自增长列相关索引的末尾添加一个排它锁。 innodb_autoinc_lock_mod=0时，InnoDB会持有一个特殊的AUTOINC表锁，直到语句执行结束（不是事务结束）。其它客户端在AUTO-INC表锁被释放之前，不能插入新的记录。innodb_autoinc_lock_mod=1时，这对于批量插入也同样适用。当innodb_autoinc_lock_mode=2时，不会使用表级别的AUTO-INC锁。想了解更多信息，请查阅章节 15.6.1.6，“AUTO_INCREMENT Handling in InnoDB”。 InnoDB获取之前已经初始化过的自增长列的值时，不会添加任何锁。
如果一张表上添加有外键，任何insert，update，或delete语句执行时，都会在要检查的相关记录上添加行级共享锁。InnoDB还会在外键约束失败的情况下设置这些锁。
lock tables设置表锁，但是这是InnoDB层之上，更高级别的MySQL层设置的锁。如果innodb_table_locks=1(默认)且autocommit=0，InnoDB能感知到表锁。MySQL层也知道行级别锁。否则，如果涉及到表锁，InnoDB的自动锁检测机制将无法检测到死锁。同样，如果MySQL层不知道行锁，它可能在其它会话持有行级锁时添加表锁。但是，这不会危及事务的完整性。详见章节15.7.5,2，“Deadlock Detection”。
如果innodb_table_locks=1(默认值)，lock tables会在每张表上获取两个锁。一个是MySQL层的表锁，另一个是InnoDB引擎层的表锁。如果不要获取InnoDB层的表锁，可以设置innodb_table_locks=0。如果没有获取InnoDB层的表锁，即使表中的部分记录被其它事务锁住，lock tables也不会阻塞。在MySQL8.0里，innodb_table_locks=0对于使用lock tables … write锁住的表无效。对于间接lock tables … write(例如，通过触发器)或者是通过lock tables … read添加的读锁货写锁，它确实有效。
所有的InnoDB锁，在事务被提交或中止之后都会被释放。因此，在autocommit=1模式下，在InnoDB表上使用lock tables锁表没有多大意义，因为锁很快就会被释放。
不能在事务中间锁定其它表，因为lock tables会进行隐式的commit和unlock tables。