带主键和表的意外锁定独特的钥匙

发布于 2024-10-15 06:28:43 字数 1012 浏览 3 评论 0原文

对于同时具有主键和单独的唯一索引的表上的事务，我遇到了 innodb 锁定问题。看起来如果 TX 使用唯一键删除一条记录，然后重新插入相同的记录，这将导致下一个键锁定而不是预期的记录锁定（因为键是唯一的）。请参阅下面的测试用例以及我期望拥有哪些锁的记录的详细信息：（

DROP TABLE IF EXISTS foo; 
CREATE TABLE `foo` ( 
  `i` INT(11) NOT NULL, 
  `j` INT(11) DEFAULT NULL, 
  PRIMARY KEY (`i`), 
  UNIQUE KEY `jk` (`j`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; 
INSERT INTO foo VALUES (5,5), (8,8), (11,11);

注意：只需在 TX1 sql 之后运行 TX2 sql，在单独的连接中）

TX1

START TRANSACTION; 
DELETE FROM foo WHERE i=8;

导致 i=8 上的独占锁（无间隙）锁，因为 i 是主键并且是唯一的）

INSERT INTO foo VALUES(8,8);

导致 i=8 & 的独占锁j= 8，并且 i=6 上的共享意图锁 & i=7，以及j=6 & j=7

TX2

START TRANSACTION; 
INSERT INTO foo VALUES(7,7);

导致 i=7 & 的独占锁定j=7，以及 i=6 上的共享意图锁定j=6

我希望 TX2 不会被 TX1 阻塞，但事实确实如此。奇怪的是，阻塞似乎与 TX1 的插入有关。我这样说是因为如果TX1的插入语句在删除后不运行，那么TX2的插入不会被阻塞。这几乎就像 TX1 重新插入 (8,8) 导致索引 j 上的 (6,8] 的下一个键锁定一样。

任何见解都将不胜感激。

原文

I've run into an innodb locking issue for transactions on a table with both a primary key and a separate unique index. It seems if a TX deletes a record using a unique key, and then re-inserts that same record, this will result in a next-key lock instead of the expected record lock (since the key is unique). See below for a test case as well as breakdown of what records I expect to have what locks:

DROP TABLE IF EXISTS foo; 
CREATE TABLE `foo` ( 
  `i` INT(11) NOT NULL, 
  `j` INT(11) DEFAULT NULL, 
  PRIMARY KEY (`i`), 
  UNIQUE KEY `jk` (`j`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; 
INSERT INTO foo VALUES (5,5), (8,8), (11,11);

(Note: Just run the TX2 sql after the TX1 sql, in a separate connection)

TX1

START TRANSACTION; 
DELETE FROM foo WHERE i=8;

results in exclusive lock on i=8 (no gap lock since i is primary key and unique)

INSERT INTO foo VALUES(8,8);

results in exclusive lock for i=8 & j= 8, and shared intention lock on i=6 & i=7, as well as j=6 & j=7

TX2

START TRANSACTION; 
INSERT INTO foo VALUES(7,7);

results in exclusive lock for i=7 & j=7, as well as shared intention lock on on i=6 & j=6

I would expect TX2 to not be blocked by TX1, however it is. Oddly, the blocking seems to be related to the insert by TX1. I say this because if TX1's insert statement is not run after the delete, TX2's insert is not blocked. It's almost as if TX1's re-insertion of (8,8) causes a next-key lock on index j for (6,8].

Any insight would be much appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

归途 2024-10-22 06:28:43

您遇到的问题的发生是因为 MySQL 不仅会锁定您要插入的值的表行，还会按顺序锁定上一个 id 和下一个 id 之间的所有可能值，因此，重用下面的示例：

DROP TABLE IF EXISTS foo;
CREATE TABLE `foo` (
  `i` INT(11) NOT NULL,
  `j` INT(11) DEFAULT NULL,
  PRIMARY KEY (`i`),
  UNIQUE KEY `jk` (`j`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
INSERT INTO foo VALUES (5,5), (8,8), (11,11);

假设您从事务 TX1 开始：

START TRANSACTION;
REPLACE INTO foo VALUES(8,8);

然后，如果您开始事务 TX2，无论使用 INSERT 或 REPLACE 5 到 11 之间的 >id 将被锁定：

START TRANSACTION;
REPLACE INTO foo VALUES(11,11);

看起来 MySQL 使用这种锁定来避免此处描述的“幻象问题”：http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html< /a>，MySQL使用“下一个键锁定”，它将索引行锁定与间隙锁定相结合，这对我们来说意味着它将锁定上一个和下一个id之间的许多可能的id，并将锁定上一个和下一个id 也是如此。

为了避免这种情况，请尝试创建一个插入记录的服务器算法，以便在不同事务中插入的记录不会重叠，或者至少不会同时执行所有事务，这样 TX 就不会发生这种情况。不必互相等待。

The problem you are experiencing happens because MySQL doesn't just lock the table row for a value you're going to insert, it locks all possible values between the previous id and the next id in order, so, reusing your example bellow:

DROP TABLE IF EXISTS foo;
CREATE TABLE `foo` (
  `i` INT(11) NOT NULL,
  `j` INT(11) DEFAULT NULL,
  PRIMARY KEY (`i`),
  UNIQUE KEY `jk` (`j`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
INSERT INTO foo VALUES (5,5), (8,8), (11,11);

Suppose you start with transaction TX1:

START TRANSACTION;
REPLACE INTO foo VALUES(8,8);

Then if you start a transaction TX2, whatever INSERT or REPLACE using an id between 5 and 11 will be locked:

START TRANSACTION;
REPLACE INTO foo VALUES(11,11);

Looks like MySQL uses this kind of locking to avoid the "phantom problem" described here: http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html, MySQL uses a "next-key locking", that combines index-row locking with gap locking, this means for us that it will lock a lot of possible ids between the previous and next ids, and will lock prev and next ids as well.

To avoid this try to create a server algorithm that inserts your records so that records inserted in different transactions don't overlap, or at least don't execute all your transactions at the same time so the TX doesn't have to wait one each other.

回复收藏 0 原文