不同sql语句的对应的锁介绍

abments

已于 2022-09-06 17:27:56 修改

阅读量221

点赞数

于 2022-09-06 17:19:55 首次发布

原文链接：https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of an SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned. The locks are normally next-key locks that also block inserts into the “gap” immediately before the record. However, gap locking can be disabled explicitly, which causes next-key locking not to be used. For more information, see Section 14.7.1, “InnoDB Locking”. The transaction isolation level can also affect which locks are set; see Section 14.7.2.1, “Transaction Isolation Levels”.

译文：

锁的读取、更新或删除通常会在SQL语句扫描过程中把行记录锁设置到每个记录的索引上。语句中是否存在排除该行的WHERE条件并不重要。InnoDB不记录确切的WHERE条件，只知道扫描了哪些索引范围。这些锁通常是临界锁，也会阻止插入记录之前的“间隙”。但是，可以显式禁用间隙锁定，这会导致不使用临界锁。有关更多信息，请参阅第14.7.1节“InnoDB锁定”。事务隔离级别也会影响设置了哪些锁；参见第14.7.2.1节“事务隔离级别”。

官网原文如下:

If a secondary index is used in a search and the index record locks to be set are exclusive, InnoDB also retrieves the corresponding clustered index records and sets locks on them.
If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not scan more rows than necessary.

译文:

如果在搜索中使用辅助索引，并且索引记录锁被设立为排他锁，InnoDB还会检索相应的聚集索引记录并对其设置锁。
如果没有适合您的语句的索引，并且MySQL必须扫描整个表以处理该语句，则表中的每一行都会被锁定，这反过来会阻止其他用户对表的所有插入。重要的是要创建良好的索引，以便查询不会扫描超过需要的行。

官网原文如下:

InnoDB sets specific types of locks as follows.

译文:

InnoDB设置特定类型的锁，如下所示。

SELECT…FROM语句

官网原文：

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…FROM是一致读取，读取数据库的快照，除非事务隔离级别设置为SERIALIZABLE,否则不设置锁。对于SERIALIZABLE级别，搜索会在遇到的索引记录上设置共享临界锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁定。

对于SELECT…For UPDATE

官网原文：

For SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE, locks are acquired for scanned rows, and expected to be released 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或SELECT…LOCK IN SHARE模式，将为扫描的行获取锁，并为不符合结果集中包含条件的行释放锁（例如，如果它们不符合WHERE子句中给出的条件）。但是，在某些情况下，行可能不会立即解锁，因为在查询执行期间，结果行与其原始源之间的关系丢失。例如，在UNION中，在评估表中扫描（和锁定）的行是否符合结果集之前，可以将其插入临时表中。在这种情况下，临时表中的行与原始表中的行之间的关系将丢失，并且在查询执行结束之前，不会解锁后一行。

官网原文：

SELECT … LOCK IN SHARE MODE sets shared next-key locks on all index records 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.

译文：

在SELECT … LOCK IN SHARE MODE 会在遇到的所有索引记录上设置共享临界锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要对索引记录加锁。

官网原文：

SELECT … FOR UPDATE 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.
For index records the search encounters, SELECT … FOR UPDATE blocks other sessions from doing SELECT … LOCK IN SHARE MODE or from reading in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view.

译文：

SELECT … FOR UPDATE 会在遇到的所有索引记录上设置排他临界锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁定。
对于搜索遇到的索引记录，SELECT … FOR UPDATE将阻止其他会话在共享模式下 SELECT … LOCK IN SHARE MODE锁定或在某些事务隔离级别读取。一致读取忽略在读取视图中存在的记录上设置的任何锁定。

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.
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.

译文：

UPDATE…WHERE…在搜索遇到的每个记录上设置一个排他的临界锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁定。
更新修改聚集索引记录时，会对受影响的辅助索引记录执行隐式锁。在插入新的辅助索引记录之前执行重复检查扫描时，以及在插入新辅助索引记录时，更新操作也会对受影响的辅助索引纪录采取共享锁。

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.

译文：

DELETE FROM … WHERE … ，在搜索遇到的每个记录上设置排他的临界键锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁定。

官网原文：

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.

INSERT 语句

译文：

INSERT在插入的行上设置排他锁。此锁是索引记录锁，而不是临界锁（即没有间隙锁），并且不阻止其他会话在插入行之前插入间隙。

官网原文：

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.

译文：

在插入行之前，将设置一种称为插入意图间隙锁的间隙锁。该锁以这样一种方式发出插入意图的信号，即插入到同一索引间隙中的多个事务如果不在间隙中的同一位置插入，则不需要彼此等待。假设存在值为4和7的索引记录。尝试插入值为5和6的单独事务在获得插入行的排他锁之前，都会使用插入意图锁锁定4和7之间的间隙，但不会相互阻止，因为行不冲突。

官网原文:

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:

译文：

如果出现重复key错误，则在重复索引记录上设置共享锁。如果有多个会话试图插入同一行，而另一个会话已经具有独占锁，则使用共享锁可能导致死锁。如果另一个会话删除该行，则可能会发生这种情况。假设InnoDB表t1具有以下结构：

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

Now suppose that three sessions perform the following operations in order:

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;

官网原文：

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.
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的第一个操作获取该行的独占锁。会话2和会话3的操作都会导致重复key错误，并且它们都会请求该行的共享锁。当会话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;

官网原文：

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.

译文：

会话1的第一个操作获取该行的独占锁。会话2和会话3的操作都会导致重复key错误，并且它们都会请求该行的共享锁。当会话1提交时，它释放对该行的排他锁，并授予会话2和3的排队共享锁请求。此时，会话2和会话3处于死锁状态：由于另一方持有共享锁，因此双方都无法获得该行的独占锁。

INSERT… ON DUPLICATE KEY UPDATE

官网原文：

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.

译文：

INSERT…ON DUPLICATE KEY UPDATE与简单INSERT的不同之处在于，当出现重复key错误时，在要更新的行上放置的是独占锁，而不是共享锁。对重复的主键值采用排他索引记录锁。对于重复的唯一键值，将采用排他临界锁。

官网原文：

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.

译文：

如果唯一键上没有冲突，则REPLACE操作与INSERT 操作类似。否则，将在要替换的行上放置独占的临界锁。

INSERT INTO T SELECT … FROM S WHERE …

官网原文：

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, or innodb_locks_unsafe_for_binlog is enabled and the transaction isolation level is not SERIALIZABLE, 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 … performs the SELECT with shared next-key locks or as a consistent read, as for INSERT … SELECT.

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.

译文：

INSERT INTO T SELECT … FROM S WHERE … ，在插入T的每一行上设置一个排他索引记录锁（不带间隙锁）。如果事务隔离级别为READ COMMITTED，或innodb_locks_unsafe_for_binlog已启用，且事务隔离级别不是SERIALIZABLE，innodb将在S表上通过一致读（无锁）搜索数据。否则，InnoDB将对来自S的行设置共享的临界锁。在后一种情况下，InnoDB必须设置锁：在使用基于语句的binary log 进行前滚恢复期间，必须以与最初完全相同的方式执行每个SQL语句。

CREATE TABLE…SELECT…使用共享的临界锁或作为一致读取进行查询，INSERT…SELECT也是如此。

当SELECT 在下面语句结构中使用时，REPLACE INTO t SELECT … FROM s WHERE … 或 UPDATE t … WHERE col IN (SELECT … FROM s …)，InnoDB在表s中的行上设置共享临界锁。

官网原文：

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.

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 14.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

InnoDB fetches the value of a previously initialized AUTO_INCREMENT column without setting any locks.

译文：

InnoDB在初始化表上先前指定的AUTO_INCREMENT 列时，在与AUTO_ INCREMENT 列相关联的索引末尾设置排他锁。

当innodb_autoinc_lock_mode=0时，innodb使用一种特殊的AUTO-INC表锁模式，在访问自动递增计数器时，锁被获取并保持到当前SQL语句的末尾（而不是整个事务的末尾）。在保持AUTO-INC表锁时，其他客户端无法插入表。对于innodb_autoinc_lock_mode=1的“bulk inserts”，也会发生相同的行为。innodb-autoinc-lock_ mode=2不会发生表级自动INC锁。有关更多信息，请参阅第14.6.1.6节“innodb中的自动增量处理”。

InnoDB获取先前初始化的AUTO_INCREMENT列的值，而不设置任何锁。

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.

如果在表上定义了外键约束，则任何需要检查约束条件的插入、更新或删除操作都会在检查约束时查看的记录上设置共享记录级锁。InnoDB还在约束失败的情况下设置这些锁。

LOCK TABLES

官网原文：

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.

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 14.7.5.2, “Deadlock Detection”.

译文：

LOCK TABLES设置表锁，但设置这些锁的是InnoDB层之上的更高MySQL层。如果InnoDB_table_locks=1（默认值）和autocommit=0，InnoDB知道表锁，并且InnoDB上面的MySQL层能感知到行级锁。
否则，InnoDB的自动死锁检测无法检测涉及此类表锁的死锁。此外，因为在这种情况下，较高的MySQL层不知道行级锁，所以可以在另一个会话当前具有行级锁的表上获得表锁。但是，如第14.7.5.2节“死锁检测”所述，这不会危及事务完整性。

官网原文：

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.

In MySQL 5.7, 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_table_locks=1（默认值），则LOCK TABLES在每个表上获取两个锁。除了MySQL层上的表锁之外，它还获得了InnoDB表锁。要避免获取InnoDB表锁，请将InnoDB_table_locks设置为0。如果未获取InnoDB表锁，则即使表的某些记录被其他事务锁定，锁定表也会完成。

在MySQL 5.7中，innodb_table_locks=0对使用LOCK tables…WRITE显式锁定的表无效。对于通过锁定表…隐式写入（例如，通过触发器）或通过锁定表…“读取”锁定为读取或写入的表，它确实有影响。

官网原文：

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.