SELECT ... FROM 是一致性读取，它读取数据库的快照，并且不设置任何锁，除非事务隔离级别设置为 SERIALIZABLE。对于 SERIALIZABLE 级别，搜索会在遇到的索引记录上设置共享的下一个键锁。但是，对于使用唯一索引搜索唯一行来锁定行的语句，只需要索引记录锁。SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

SELECT ... FOR UPDATE 和 SELECT ... FOR SHARE 语句使用唯一索引时，会对扫描到的行加锁，并且释放那些不符合结果集条件的行的锁（例如，如果它们不满足 WHERE 子句中给定的条件）。然而，在某些情况下，行可能不会立即解锁，因为在查询执行过程中，结果行与其原始来源之间的关系丢失。例如，在 UNION 中，来自表的扫描（和锁定）行可能会被插入到临时表中，然后再评估它们是否符合结果集条件。在这种情况下，临时表中的行与原始表中的行之间的关系丢失，而后者的行要到查询执行结束时才会被解锁。SELECT ... FOR UPDATE and SELECT ... FOR SHARE statements that use a unique index acquire locks for scanned rows, and release the locks for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in the WHERE clause). However, in some cases, rows might not be unlocked immediately because the relationship between a result row and its original source is lost during query execution. For example, in a UNION, scanned (and locked) rows from a table might be inserted into a temporary table before evaluating whether they qualify for the result set. In this circumstance, the relationship of the rows in the temporary table to the rows in the original table is lost and the latter rows are not unlocked until the end of query execution.

对于锁定读取（SELECT 使用 FOR UPDATE 或 FOR SHARE）、UPDATE 和 DELETE 语句，所获取的锁取决于语句是使用唯一索引和唯一搜索条件，还是使用范围类型的搜索条件。For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, the locks that are taken depend on whether the statement uses a unique index with a unique search condition or a range-type search condition.

对于索引记录，搜索遇到的 SELECT ... FOR UPDATE 会阻止其他会话执行 SELECT ... FOR SHARE 或在某些事务隔离级别下读取。一致性读取会忽略在读取视图中存在的记录上设置的任何锁。For index records the search encounters, SELECT ... FOR UPDATE blocks other sessions from doing SELECT ... FOR SHARE or from reading in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view.

UPDATE ... WHERE ... 在搜索遇到的每条记录上设置独占的下一个键锁。然而，对于使用唯一索引搜索唯一行来锁定行的语句，只需要索引记录锁。UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

当UPDATE 修改聚集索引记录时，会对受影响的二级索引记录隐式加锁。在执行重复检查扫描以在插入新的二级索引记录之前，以及在插入新的二级索引记录时，UPDATE操作还会对受影响的二级索引记录加共享锁。When UPDATE modifies a clustered index record, implicit locks are taken on affected secondary index records. The UPDATE operation also takes shared locks on affected secondary index records when performing duplicate check scans prior to inserting new secondary index records, and when inserting new secondary index records.

DELETE FROM ... WHERE ... 会对搜索过程中遇到的每条记录设置独占的下一个键锁。然而，对于使用唯一索引搜索唯一行以锁定行的语句，只需要加索引记录锁。DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

INSERT 对插入的行设置一个排他锁。这个锁是索引记录锁，而不是下一个键锁（即没有间隙锁），并且不会阻止其他会话在插入行之前向该间隙插入数据。INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

在插入行之前，会设置一种称为插入意向间隙锁的间隙锁。该锁表示插入的意图，这样多个事务在同一索引间隙中插入数据时，如果它们不是在同一位置插入，就不需要互相等待。假设索引记录的值为 4 和 7。尝试插入值为 5 和 6 的不同事务在获得对插入行的排他锁之前，会分别在 4 和 7 之间的间隙上设置插入意向锁，但不会互相阻塞，因为这些行不冲突。Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

如果发生重复键错误，将在重复索引记录上设置共享锁。如果存在多个会话尝试插入相同的行，而另一个会话已经持有排他锁，则共享锁的使用可能导致死锁。如果另一个会话删除了该行，也可能发生这种情况。假设一个 InnoDB 表 t1 具有以下结构：If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row. Suppose that an InnoDB table t1 has the following structure:

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

现在假设三个会话按顺序执行以下操作：Now suppose that three sessions perform the following operations in order:

会话 1：Session 1:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话 2：Session 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话 3：Session 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话 1：Session 1:

ROLLBACK;

会话 1 的第一次操作为该行获取了排他锁。会话 2 和会话 3 的操作都导致重复键错误，并且它们都请求该行的共享锁。当会话 1 回滚时，它释放了对该行的排他锁，并且队列中等待的会话 2 和会话 3 的共享锁请求被授予。此时，会话 2 和会话 3 发生死锁：由于对方持有的共享锁，它们都无法获取该行的排他锁。The first operation by session 1 acquires an exclusive lock for the row. The operations by sessions 2 and 3 both result in a duplicate-key error and they both request a shared lock for the row. When session 1 rolls back, it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted. At this point, sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for the row because of the shared lock held by the other.

如果表中已经包含键值为 1 的行，并且三个会话按以下顺序执行操作，也会出现类似情况：A similar situation occurs if the table already contains a row with key value 1 and three sessions perform the following operations in order:

会话 1：Session 1:

START TRANSACTION;
DELETE FROM t1 WHERE i = 1;

会话 2：Session 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话 3：Session 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

会话 1：Session 1:

COMMIT;

会话 1 的第一次操作为该行获取了排他锁。会话 2 和会话 3 的操作都导致重复键错误，并且它们都请求该行的共享锁。当会话 1 提交时，它释放了该行的排他锁，并且排队等待的会话 2 和会话 3 的共享锁请求被授予。此时，会话 2 和会话 3 发生死锁：由于对方持有共享锁，它们都无法为该行获取排他锁。The first operation by session 1 acquires an exclusive lock for the row. The operations by sessions 2 and 3 both result in a duplicate-key error and they both request a shared lock for the row. When session 1 commits, it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted. At this point, sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for the row because of the shared lock held by the other.

INSERT ... ON DUPLICATE KEY UPDATE 与简单的 INSERT 不同，当发生重复键错误时，会对要更新的行施加排他锁而不是共享锁。对于重复的主键值，将获取排他索引记录锁。对于重复的唯一键值，将获取排他下一个键锁。INSERT ... ON DUPLICATE KEY UPDATE differs from a simple INSERT in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs. An exclusive index-record lock is taken for a duplicate primary key value. An exclusive next-key lock is taken for a duplicate unique key value.

REPLACE 的操作类似于 INSERT 如果在唯一键上没有冲突。否则，将对要替换的行施加排他下一个键锁。REPLACE is done like an INSERT if there is no collision on a unique key. Otherwise, an exclusive next-key lock is placed on the row to be replaced.

INSERT INTO T SELECT ... FROM S WHERE ... 会在插入到 T 的每一行上设置一个独占索引记录锁（不设置间隙锁）。如果事务隔离级别是 READ COMMITTED，InnoDB 会对 S 进行一致性读取（不加锁）。否则，InnoDB 会对 S 的行设置共享的下一键锁。InnoDB 必须在后一种情况下设置锁：在使用基于语句的二进制日志进行回滚前的恢复时，每条 SQL 语句都必须以原始执行方式完全相同的方式执行。INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: During roll-forward recovery using a statement-based binary log, every SQL statement must be executed in exactly the same way it was done originally.

CREATE TABLE ... SELECT ... 会像 SELECT 一样执行，使用共享的下一个键锁或作为一致性读取，就像 INSERT ... SELECT那样。CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT.

当在结构 REPLACE INTO t SELECT ... FROM s WHERE ... 或 SELECT 中使用 UPDATE t ... WHERE col IN (SELECT ... FROM s ...) 时，InnoDB 会对表 s 中的行设置共享的下一个键锁。When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s.

InnoDB 在初始化表上先前指定的 AUTO_INCREMENT 列时，会在与该 AUTO_INCREMENT 列相关联的索引末尾设置排他锁。InnoDB sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT column while initializing a previously specified AUTO_INCREMENT column on a table.

使用 innodb_autoinc_lock_mode=0 时，InnoDB 使用一种特殊的 AUTO-INC 表锁模式，在访问自增计数器时，该锁会被获取并保持到当前 SQL 语句结束（而不是整个事务结束）。在持有 AUTO-INC 表锁期间，其他客户端无法向表中插入数据。同样的行为也适用于 “批量插入”，配置为 innodb_autoinc_lock_mode=1 时。表级 AUTO-INC 锁在 innodb_autoinc_lock_mode=2 时不使用。更多信息，请参见 第 17.6.1.6 节，“InnoDB 中的 AUTO_INCREMENT 处理”。With innodb_autoinc_lock_mode=0, InnoDB uses a special AUTO-INC table lock mode where the lock is obtained and held to the end of the current SQL statement (not to the end of the entire transaction) while accessing the auto-increment counter. Other clients cannot insert into the table while the AUTO-INC table lock is held. The same behavior occurs for “bulk inserts” with innodb_autoinc_lock_mode=1. Table-level AUTO-INC locks are not used with innodb_autoinc_lock_mode=2. For more information, See Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

InnoDB 在不设置任何锁的情况下获取先前初始化的 AUTO_INCREMENT 列的值。InnoDB fetches the value of a previously initialized AUTO_INCREMENT column without setting any locks.

如果在表上定义了 FOREIGN KEY 约束，任何需要检查约束条件的插入、更新或删除操作都会在它检查约束的记录上设置共享记录级锁。当约束检查失败时，InnoDB 也会设置这些锁。If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. InnoDB also sets these locks in the case where the constraint fails.

LOCK TABLES 设置表锁，但这是位于 InnoDB 层之上的 MySQL 高层设置的锁。InnoDB 会在 innodb_table_locks = 1（默认值）且 autocommit = 0 时感知表锁，而位于 InnoDB 之上的 MySQL 层则了解行级锁。LOCK TABLES sets table locks, but it is the higher MySQL layer above the InnoDB layer that sets these locks. InnoDB is aware of table locks if innodb_table_locks = 1 (the default) and autocommit = 0, and the MySQL layer above InnoDB knows about row-level locks.

否则，如果涉及这样的表锁，InnoDB 的自动死锁检测将无法检测到死锁。另外，由于在这种情况下更高层的 MySQL 并不知道行级锁，因此有可能在另一个会话当前拥有行级锁的表上获取表锁。不过，这不会危及事务的完整性，如 第 17.7.5.2 节“死锁检测” 中所讨论的。Otherwise, InnoDB's automatic deadlock detection cannot detect deadlocks where such table locks are involved. Also, because in this case the higher MySQL layer does not know about row-level locks, it is possible to get a table lock on a table where another session currently has row-level locks. However, this does not endanger transaction integrity, as discussed in Section 17.7.5.2, “Deadlock Detection”.

LOCK TABLES 如果 innodb_table_locks=1（默认值），会对每个表获取两个锁。除了 MySQL 层的表锁之外，它还会获取一个 InnoDB 表锁。要避免获取 InnoDB 表锁，请将 innodb_table_locks=0 设置为 0。如果没有获取 InnoDB 表锁，即使某些表记录正被其他事务锁定，LOCK TABLES 也会完成。LOCK TABLES acquires two locks on each table if innodb_table_locks=1 (the default). In addition to a table lock on the MySQL layer, it also acquires an InnoDB table lock. To avoid acquiring InnoDB table locks, set innodb_table_locks=0. If no InnoDB table lock is acquired, LOCK TABLES completes even if some records of the tables are being locked by other transactions.

在 MySQL 8.4 中，innodb_table_locks=0 对通过 LOCK TABLES ... WRITE 显式锁定的表没有效果。它对通过 LOCK TABLES ... WRITE 隐式锁定的表（例如，通过触发器）或通过 LOCK TABLES ... READ 锁定的表有效。In MySQL 8.4, innodb_table_locks=0 has no effect for tables locked explicitly with LOCK TABLES ... WRITE. It does have an effect for tables locked for read or write by LOCK TABLES ... WRITE implicitly (for example, through triggers) or by LOCK TABLES ... READ.

事务提交或中止时，会释放该事务持有的所有 InnoDB 锁。因此，在 autocommit=1 模式下，对 InnoDB 表调用 LOCK TABLES 并没有太大意义，因为获取的 InnoDB 表锁会立即被释放。All InnoDB locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke LOCK TABLES on InnoDB tables in autocommit=1 mode because the acquired InnoDB table locks would be released immediately.

您无法在事务中途锁定额外的表，因为LOCK TABLES 会隐式执行 COMMIT 和 UNLOCK TABLES。You cannot lock additional tables in the middle of a transaction because LOCK TABLES performs an implicit COMMIT and UNLOCK TABLES.