MySQL在重复密钥错误中锁定 [英] MySQL locking in Duplicate Key Error
问题描述
从文档中:
如果发生重复键错误，则重复索引上的共享锁 记录已设置.共享锁的这种使用可能会导致死锁， 如果有另一个会话，则有多个会话尝试插入同一行 会话已经具有排他锁.如果另一个发生这种情况 会话删除该行.
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.
使用文档中的示例
假设InnoDB表t1具有以下结构:
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:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
会议2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
会议3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
会议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)插入查询对其插入的行进行排他锁.因此，假设T1正在插入第1行，它将锁定第1行.现在，当T2开始写入时，INNODB将在执行查询之前对查询进行评估，并发现它将要写入相同的PK(i = 1的行)并让T2等待?或者它将开始执行T2并发现它给出了重复的键错误或PK违反.
1) The insert query takes an exclusive lock on the row it is inserting. So, suppose T1 is inserting on row 1, it will lock row 1. Now when T2 comes to write, will INNODB evaluate the query before executing it and find out that it is going to write the same PK (row with i = 1) and make T2 wait? Or will it start execution of T2 and find that it gives duplicate key error or PK violation.
2)为什么T2和T3采取共享锁?插入过程中共享锁如何出现?
2) Why are T2 and T3 taking shared locks? How do shared locks come into picture during insert?
推荐答案
1)插入查询对它所在的行进行排他锁 插入.因此，假设T1正在插入第1行，它将锁定第1行. 现在，当T2写入时，INNODB会先评估查询 执行它，发现它要写相同的PK(行 与我= 1)，并让T2等待?还是将开始执行T2并 发现它给出了重复的密钥错误或PK违规.
1) The insert query takes an exclusive lock on the row it is inserting. So, suppose T1 is inserting on row 1, it will lock row 1. Now when T2 comes to write, will INNODB evaluate the query before executing it and find out that it is going to write the same PK (row with i = 1) and make T2 wait? Or will it start execution of T2 and find that it gives duplicate key error or PK violation.
我认为您正在简化术语/过程.在查询被解析之后并且在执行之前，它需要获取必要的锁.在这一点上确定:
I think you are simplifying the terminology/process. After the query is parsed and before it is executed, it needs to acquire necessary locks. It is at this point that it is determined that:
- 会话1获得排他锁，因为它正在插入并且没有其他锁
- 会话2和3排队等待共享锁，因为排他锁已由会话1持有，并且会话2和3处于重复密钥错误中
2)为什么T2和T3采取共享锁?共享锁如何来 插入图片时进入图片?
2) Why are T2 and T3 taking shared locks? How do shared locks come into picture during insert?
按上述，会话2和3排队等待共享锁，因为它们存在重复的密钥错误.但是，当会话1删除密钥并释放排他锁时，现在会话2和3都被授予共享锁.此时，两者都尝试获取排他锁以完成插入.但是，任何一个都不能，因为另一个已经持有了共享锁.因此，互斥锁也不会授予两者，并且会死锁.
Per above, sessions 2 and 3 get queued for shared locks because they are in duplicate key error. However, when session 1 deletes the key and releases the exclusive lock, now both session 2 and 3 get granted shared locks. At this point both try to acquire an exclusive lock to complete the insert. Neither one can, though, because the other is already holding the shared lock. So exclusive lock is not granted to either and they deadlock.
这篇关于MySQL在重复密钥错误中锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
