MySQL重复键错误导致在重复索引记录上设置了共享锁? [英] MySQL duplicate key error causes a shared lock set on the duplicate index record?

问题描述

我已经阅读了MySQL 14.2.7.6的文档.InnoDB中由不同的SQL语句设置的锁

I've read the document of MySQL 14.2.7.6. Locks Set by Different SQL Statements in InnoDB

http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html

我的问题:

1. 我不明白为什么文档说明:

1. I don't understand why documentation states:

如果发生重复键错误，则重复索引上的共享锁记录已设置.共享锁的这种使用可能会导致死锁，如果有另一个会话，则有多个会话尝试插入同一行会话已经具有排他锁.如果另一个发生这种情况会话删除该行.

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.

为什么由于INSERT操作已经在行上设置了锁定失败的.它为什么获得了锁?

Why does it set a lock on the row since the INSERT operation has failed. It acquires the lock for what?

正在执行选择...共享模式锁定"时是否设置了意图共享(IS)锁定?是意向排他(IX)锁集当"UPDATE，INSERT，DELETE"或"SELECT ... FOR UPDATE"为正在执行?

Is an Intention shared (IS) lock set when "SELECT ... LOCK IN SHARE MODE" is executing? Is an Intention exclusive (IX) Lock set when "UPDATE, INSERT, DELETE" or "SELECT ... FOR UPDATE" are executing?

推荐答案

.1.它需要锁定现有条目，以便后续尝试插入重复记录的尝试始终失败:

.1. It requires a lock on the existing entry so that subsequent attempts to insert a duplicate record fail consistently:

-- Transaction A
BEGIN TRANSACTION;
INSERT INTO mytable VALUE(1); -- fails as "duplicate"

-- Transaction B
BEGIN;
DELETE FROM mytable WHERE field = 1; -- must be put on hold, see below

-- Transaction A
-- transaction is still in progress
INSERT INTO mytable VALUE(1); -- must fail to stay consistent with the previous attempt

.2.是的，是的:

意图锁定协议如下:

The intention locking protocol is as follows:

• 在交易可以获取表 t 上的行的 S 锁之前，它必须首先获得 IS 或 t .
• 交易必须先获得 t 上的 IX 锁，然后才能连续获得 X 锁.

• Before a transaction can acquire an S lock on a row in table t, it must first acquire an IS or stronger lock on t.
• Before a transaction can acquire an X lock on a row, it must first acquire an IX lock on t.

这篇关于MySQL重复键错误导致在重复索引记录上设置了共享锁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！