MySQL重复键错误导致在重复索引记录上设置了共享锁?

问题描述

我已经阅读了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:

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

Why does it set a lock on the row since the INSERT operation hasfailed. 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 setwhen "UPDATE, INSERT, DELETE" or "SELECT ... FOR UPDATE" areexecuting?

推荐答案

.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.是的，是的:

• 在交易可以获取表 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重复键错误导致在重复索引记录上设置了共享锁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！