Locks Set by Different SQL Statements in InnoDB

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.3.5.6, “InnoDB Record, Gap, and Next-Key Locks”. The transaction isolation level also can affect which locks are set; see Section 13.3.6, “SET TRANSACTION Syntax”.

一个加锁的read, update, 或者delete通常SQL语句会在每个扫描和处理到的索引记录上加上锁.无论语句中有没有排除这些行的WHERE条件.InnoDB不记得具体的WHERE条件, 它只知道被扫描的索引范围.这些所通常是next-key locks, 他们会阻止记录行之前的间隙的插入操作.然而, gap locking可以被显示禁用.

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将会获取聚簇索引并在他们上面加锁.

Differences between shared and exclusive locks are described in Section 14.3.5.3, “InnoDB Lock Modes”.

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肯定会扫描整个表来处理语句, 这样每一行都会被锁住, 这样会导致所有其他用户的insert语句被阻塞.所以建立正确的索引很重要.

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 aUNION, 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, 在扫描行的时候需要获取锁, 但是不包含在结果集里的行的锁会被释放(例如, 如果他们不满足WHERE条件).然而, 在某些情况下, 行可能不会马上解锁, 因为结果行和原始源的关系在查询过程中丢失了.例如, 在UNION时, 扫描表中的(加锁)行可能会在被评估是否符合结果集条件之前,插入到一个临时表.这种情况下, 临时表里行的关系会被丢失,  这样后面的行可能不会被释放直到查询结束

InnoDB sets specific types of locks as follows.

InnoDB设置如下指定类型的锁

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

• SELECT ... FROM 是一致性读, 读取数据库的快照, 并且不会设置任何锁, 除非数据库事务隔离级别设置为 SERIALIZABLE. 对于 SERIALIZABLE 级别, 查询集合在遇到的索引记录上设置 shared next-key locks

• SELECT ... FROM ... LOCK IN SHARE MODE sets shared next-key locks on all index records the search encounters.

• SELECT ... FROM ... LOCK IN SHARE MODE 会在遇到的所有索引记录上设置 shared next-key locks

• For index records the search encounters, SELECT ... FROM ... FOR UPDATE blocks other sessions from doingSELECT ... FROM ... LOCK IN SHARE MODE or from reading in certain transaction isolation levels. Consistent reads will ignore any locks set on the records that exist in the read view.

• SELECT ... FROM ... FOR UPDATE 会阻塞其他会话执行 SELECT ... FROM .. LOCK IN SHARE MODE 或者 在特定事务隔离级别下阻止读取操作. 一致性读会忽视任何设置在记录上的任何锁.

• UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

• UPDATE ... WHERE ... 设置一个 exclusive next-key lock 在每个他查找是遇到的行里.

• DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

• DELETE FROM ... WHERE ... 同 UPDATE.

• 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 设置一个exclusive lock 在插入行上. 这个锁是一个index-record lock, 不是一个 next-key lock(也就是说, 没有间隙锁)并且不会阻止其他会话插入数据到插入行之前.

  Prior to inserting the row, a type of gap lock called an insertion 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.

• 在插入行之前, 一种叫做insertion intention gap lock的锁会被设置. 这个锁表示打算插入操作可以这么搞: 多个事务插入同一个索引间隙不需要相互等待, 只要他们不是插入到同一个间隙的同一个点. 假如有索引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:

• 如果一个duplicate-key错误发生了, 重复index记录上会设置shared lock.如果有一个session已经有一个exclusive lock, 但是又有多个session 试图插入同一行 , 这种shared lock 的用法会导致死锁. 这在另一个session删除了这行的时候可能发生.如果innodb表t1有如下结构

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

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

• 现在假设有3个session执行如下语句

  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.

• session1第一个操作获取一个排他锁. session 2, 3 的操作会造成duplicate-key错误, 并且都会请求一个那行的shared lock. 当session 1 回滚, 他会释放他的排他锁, 并且session2,3 的shared lock请求会被授权.这是, session 2 3 会死锁: 因为shared lock被对方持有, 所以他们俩个人都无法获取排他锁.

  A similar situation occurs if the table already contains a row with key value 1 and three sessions perform the following operations in order:

• 一个类似的状况是, 如果表已经包含了一个key为1的行, 并且3个session按顺序执行如下操作:

  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.

• 由session 1的操作获取一个排他锁. session 2 和 3 的操作会导致duplicate-key error, 并且他们都请你去了一个这一行的共享锁.当session1提交时, 他释放排他锁并且session2和3 的锁会获得授权. 这是, session2和3死锁了: 谁都不能获取排他锁, 因为他们的共享锁都由对方持有

• INSERT ... ON DUPLICATE KEY UPDATE differs from a simple INSERT in that an exclusive next-key lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs.

• INSERT ... ON DUPLICATE KEY UPDATE 和 简单的INSERT相比有所不同, 他在发生duplicate-key错误的时候, 产生的是exclusive next-key lock而不是shared lock.

• 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在没有其他unique key冲突的时候的会和insert一样完成操作. 否则会在那一行上放置next-key lock.

• INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record 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: In roll-forward recovery from a backup, every SQL statement must be executed in exactly the same way it was done originally.

• INSERT INTO T SELECT ... FROM S WHERE ... 设置了一个没有间隙锁的排他索引记录. 如果事务隔离级别是READ_COMMITTED或者innodb_locks_unsafe_for_binlog开启了, 并且事务隔离级别不是SERIALIZABLE, InnoDB查找S时则不是一致性读(没有锁). 另外, InnoDB在S上设置了shared next-key locks. InnoDB必须在后面这种情况必须设锁:  在备份的前滚恢复是, 每条sql必须按照他原来完成的形式那样执行.

  CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as forINSERT ... 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.

• While initializing a previously specified AUTO_INCREMENT column on a table, InnoDB sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT column. In accessing the auto-increment counter, InnoDBuses a specific AUTO-INC table lock mode where the lock lasts only to the end of the current SQL statement, not to the end of the entire transaction. Other sessions cannot insert into the table while the AUTO-INC table lock is held; see Section 14.3.5.2, “The InnoDB Transaction Model and Locking”.

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

• 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 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.3.5.10, “Deadlock Detection and Rollback”. See also Section 14.3.9.7, “Limits on InnoDB Tables”.