意外锁定表与主键&唯一键 [英] Unexpected Locking for Table with Primary Key & Unique Key

问题描述

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

  DROP TABLE如果EXISTS foo; 
 CREATE TABLE`foo`（
`i` INT（11）NOT NULL，
`j` INT（11）DEFAULT NULL，
 PRIMARY KEY 
 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是主键和唯一） / p>



  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的insert语句没有运行后删除，TX2的插入不会被阻塞。这几乎就好像TX1的重新插入（8,8）会导致索引j上的下一个键锁定（6,8）。

任何洞察力非常感谢。

解决方案

遇到的问题是因为MySQL不只是锁定表格行要插入，它将依次锁定前一个id和下一个id之间的所有可能的值，因此，重用您的示例：

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

假设你从事务TX1开始：

  START TRANSACTION; 
 REPLACE INTO foo VALUES（8,8）; 
  
  START TRANSACTION; 
 REPLACE INTO foo VALUES（11,11）; 
  
 
 
看起来MySQL使用这种锁定来避免这里描述的幻影问题 =http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html =noreferrer> http://dev.mysql.com/doc/refman/ 5.0 / en / innodb-next-key-locking.html ，MySQL使用下一键锁定，它结合了索引行锁定和间隙锁定，这意味着我们将锁定很多可能
 
 
 
为了避免这种情况，尝试创建一个插入记录以便插入记录的服务器算法在不同的事务中不重叠，或至少不会在同一时间执行所有的事务，所以TX不必等待一个对方。 
 
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.
解决方案
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. 

                        
这篇关于意外锁定表与主键&唯一键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！