casyup.me@outlook.com

0%

read/LocksSetbyDifferentSQLStatementsinInnoDB

Posted on Edited on

15.7.3 Locks Set by Different SQL Statements in InnoDB

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. ForSERIALIZABLE 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 具有一致读属性, 读取数据库的快照, 除非事务隔离等级设置为序列化, 否则不加锁.

    对序列化隔离等级, 搜索设置共享的 next-key 锁在遇到的索引记录上, 然而, 使用唯一索引去搜索唯一行的语句, 只有索引记录锁是必须的 (PS. 我不懂 = =… )

  • 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 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 … FOR SHARE 语句使用唯一索引获取锁扫描行, 释放不符合返回集合的锁 (比如, 如果它们不在 WHERE 子句条件中). 然而, 在某些条件下, 行可能不会被立即解锁, 因为返回行和它原始资源在查询语句执行期间丢失了. 比如, 在 UNION 语句中, 来自表的已扫描(和已锁)行在它们被评估是否符合搜索结果时, 可能被插入到临时表中, 在这样的环境下, 临时表中的行与原始表中的行的关系将会丢失, 后面的行直到查询结束后才解锁

  • 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 和 FRO UPDATE 或 FOR SHARED 配合), UPDATE, 和 DELETE 语句, 是否加锁取决于语句是否使用具有唯一搜索条件或范围类型搜索条件的唯一索引

    • For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.

      对于具有唯一搜索条件的唯一索引而言, InnoDB 只锁定查找到的索引记录, 不包含前面的间隙

    • For other search conditions, and for non-unique indexes, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 15.7.1, “InnoDB Locking”.

      对于其他搜索条件, 以及非唯一索引, InnoDB 锁定范围扫描的索引, 使用间隙锁或 next-key (PS. 我真不知道怎么翻译这个比较优雅 = =) 锁阻塞其他会话包含范围的插入

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

    搜索所遇见的索引记录, SELECT … FOR UPDATE 阻塞其他来自 SELECT … FOR SHARE 或 来自在某一隔离级别的读取操作的会话, 一致读忽略任何在读取视图中的记录上设置的锁

  • 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 锁到每个搜索遇到的记录上, 然而, 使用唯一索引查找唯一行的语句只需要一个索引记录锁

  • 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 更改聚簇索引记录时, 在受影响的次级索引上添加一个隐式锁. UPDATE 还会在插入新的次级索引记录前, 执行重复检查扫描时, 向受影响的次级索引记录上添加共享锁

  • 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 … 给每个搜索遇到的记录设置一个互斥锁, 然而, (PS. 一模一样 = =)

  • 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 在插入行设置互斥锁, 这是一个索引记录锁, 而非 next-key 锁 (也就是说, 不是间隙锁)

    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.

    在插入行之前, 一种称为插入意图间隙锁的间隙锁被设置. 这个锁表示意图一种插入方式 : 多个事务插入同样的索引间隙, 如果它们不是插入间隙中的同样位置, 那么不必等待其他事务完成 (PS. 例如, 你想插入 4 和 5 到 3 和7 之间, 4 和 5 虽然插入的是同样的间隙, 但是它们插入的位置是不同的, 所以不应该阻塞彼此) 假设有值为 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 有如下结构 : 

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

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

    Session 1:

    1
    2
    START TRANSACTION;
    INSERT INTO t1 VALUES(1);

    Session 2:

    1
    2
    START TRANSACTION;
    INSERT INTO t1 VALUES(1);

    Session 3:

    1
    2
    START TRANSACTION;
    INSERT INTO t1 VALUES(1);

    Session 1:

    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:

    1
    2
    START TRANSACTION;
    DELETE FROM t1 WHERE i = 1;

    Session 2:

    1
    2
    START TRANSACTION;
    INSERT INTO t1 VALUES(1);

    Session 3:

    1
    2
    START TRANSACTION;
    INSERT INTO t1 VALUES(1);

    Session 1:

    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.

  • 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, 当重复键错误发生时, 它获取行的互斥锁而非共享锁, 重复主键值获取互斥索引记录锁, 重复唯一键值获取互斥 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.

    如果没有唯一键碰撞, REPLACE 行为类似 INSERT. 其他情况下, 获取行的互斥 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, 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.

    INSERT INTO T SELECT … FROM S WHERE … 在每个插入到 T 的行上设置一个互斥索引记录锁.

    如果事务隔离等级是 READ COMMITTED, InnoDB 在 S 上执行一致读, 其他情况, InnoDB 在来自 S 的行上设置共享 next-key 锁. InnoDB 需要在后一种情况下设置锁 : 在 roll-forward 恢复期间, 使用基于语句的二进制日志, 每个 SQL 语句必须以与初始语句完全相同的方式执行.

    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 ...), InnoDBsets shared next-key locks on rows from table s.

    当 SELECT 在构造 REPLACE INTO t SELECT … FROM s WHERE … 或 UPDATE t … WHERE col IN (SELECR … FROM s …) 语句时, InnoDB 在 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_INCREMENTcolumn. In accessing the auto-increment counter, InnoDB uses 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 15.7.2, “InnoDB Transaction Model”.

    当初始化在表上之前指定带有 AUTO_INCREMENT 关键字的列时, InnoDB 在与 AUTO_INCREMENT 关联的索引末尾设置互斥锁. 在访问自增计数器时, InnoDB 使用特殊的 AUTO-INC 表锁模式, 锁只持续当前 SQL 语句的末尾, 而不是当前事务的末尾. 其他会话不能在 AUTO-INC 表锁已获得的情况下插入数据进该表中

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

    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.

    如果表上定义了 FOREIGN KEY 约束, 任何插入, 更新, 或删除都需要检查约束条件, 在检查约束的记录上设置共享的, 记录级锁

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

    LOCK TABLES 设置表锁, 这是高于 InnoDB 层的 MySQL 层设置的锁. 如果 innodb_table_locks = 1 (默认) 并且 autocommit = 0, 则 InnoDB 能够感知表锁, MySQL 层知道行级锁

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

    其他情况, InnoDB 的自动死锁发现机制不能察觉涉及这样的表锁的死锁, 同样, 因为在这样的情况下 MySQL 层不能察觉行级锁, 可以在另一个会话当前拥有行级锁的表上获取表锁. 然而, 这会危害事务的完整性