mysql锁官方文档与理解之InnoDB中不同的SQL语句设置的锁类型
2020-07-17 16:59 l_v_y_forever 阅读(454) 评论(0) 收藏 举报14.7.3 Locks Set by Different SQL Statements in InnoDB（InnoDB中不同的SQL语句设置的锁类型）
A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the 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 also can affect which locks are set; see Section 14.7.2.1, “Transaction Isolation Levels”.
翻译：锁定读、更新或删除通常对SQL语句处理过程中扫描的每个索引记录设置记录锁（next-key锁）。语句中是否有排除该行的条件并不重要。InnoDB不记得确切的位置条件，但只知道哪个索引范围被扫描。这些锁通常是next-key锁，它们也会阻塞插入到记录之前的“间隙”中。但是，可以显式禁用间隙锁定，这将导致不使用next-key锁定。更多信息，见14.7.1节“InnoDB锁”。事务隔离级别也会影响设置的锁;见14.7.2.1节“事务隔离级别”。
If a secondary index is used in a search and index record locks to be set are exclusive, InnoDB also retrieves the corresponding clustered index records and sets locks on them.
翻译：如果在搜索中使用了一个二级索引，并且要设置的索引记录锁是排他的，InnoDB也会检索相应的聚集索引记录并对它们设置锁。
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 unnecessarily scan many rows.
翻译：如果没有适合语句的索引，MySQL必须扫描整个表来处理语句，那么表的每一行都会被锁定，从而阻塞其他用户对表的所有插入。创建良好的索引很重要，这样您的查询就不会扫描很多行。
InnoDB sets specific types of locks as follows.
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事务隔离级别，搜索在遇到的索引记录上设置共享的next-key锁。但是，对于使用惟一索引来搜索惟一行来锁定行的语句，只需要一个索引记录锁。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 evaluation 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 MODE下，对被扫描的索引行获取锁（next-key锁），对不符合结果集中包含条件的行释放锁(例如，如果它们不满足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锁定搜索遇到的所有索引记录上的共享的next-key锁定。但是，对于使用惟一索引来搜索惟一行来锁定行的语句，只需要一个索引记录锁。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.SELECT ... FOR UPDATE，在搜索遇到的每个记录上设置一个独占的next-key锁。但是，对于使用惟一索引来搜索惟一行来锁定行的语句，只需要一个索引记录锁。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 ... LOCK IN SHARE MODE或在某些事务隔离级别的读取。一致性读取忽略在read视图中存在的记录上设置的任何锁。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 ... WHERE ... 在搜索遇到的每个记录上设置独占的next-key锁。但是，对于使用惟一索引来搜索惟一行来锁定行的语句，只需要一个索引记录锁。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 ... 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 ...在搜索遇到的每个记录上设置独占的next-key锁。但是，对于使用惟一索引来搜索惟一行来锁定行的语句，只需要一个索引记录锁。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.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:
翻译：如果出现重复键错误，则在重复索引记录上设置共享锁。如果有多个会话试图插入同一行，而另一个会话已经有排他锁，那么使用共享锁会导致死锁。如果另一个会话删除了该行，就会发生这种情况。假设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.
翻译：会话1的第一个操作为该行获取排他锁。会话2和会话3的操作都会导致重复键错误，并且它们都请求该行的共享锁。当会话1回滚时，它释放该行上的独占锁，并授予会话2和3的排队共享锁请求。此时，会话2和3死锁:由于另一方持有共享锁，这两个会话都不能获得该行的排他锁。
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;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的操作都会导致重复键错误，并且它们都请求该行的共享锁。当会话1提交时，它释放该行上的独占锁，并授予会话2和3的排队共享锁请求。此时，会话2和3死锁:由于另一方持有共享锁，这两个会话都不能获得该行的排他锁。
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 与简单插入的不同之处在于，当发生重复键错误时，将在要更新的行上放置排他锁而不是共享锁。对重复的主键值采取独占索引记录锁。对重复的唯一键值使用独占的next-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.
翻译：如果唯一键上没有冲突，替换就像插入一样完成。否则，将在要替换的行上放置一个独占的next-key锁。
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.
翻译：INSERT INTO T SELECT ... FROM S WHERE ... 在T表上已被插入的每一条记录上设置一个排他的索引记录锁（没有间隙锁） .如果事务隔离级别读已提交,或启用innodb_locks_unsafe_for_binlog同时事务隔离级别不是序列化,InnoDB在S上执行一个一致读(无锁)的搜索。否则，InnoDB在S的所有行上设置共享的next-key锁。InnoDB在后面这种情况下必须设置锁：在使用statement-based的二进制日志回滚恢复期间，每条SQL语句都必须按照与最初完全相同的方式执行。
CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT.
翻译：CREATE TABLE ... SELECT ... 使用共享的next-key锁或作为一致的读取来执行SELECT，例如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.
翻译：当在使用SELECT构造REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...)时，InnoDB在表s中的行上设置了共享的next-key锁。
参考mysql官方文档链接：
https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html
浙公网安备 33010602011771号