Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Unexpected Locking for Table with Primary Key & Unique Key

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.

like image 203
Jake McGraw Avatar asked Jan 31 '11 15:01

Jake McGraw


People also ask

How do you stop a table from locking?

Keep transactions that modify data as short as possible. The longer the transaction, the longer the exclusive or update locks are held. This blocks other activity and can lead to an increased number of deadlock situations. Keep transactions in one batch.

How do I stop a MySQL table from locking?

Third option to prevent table locks with MySQL database is to use AUTOCOMMIT on the database level. This will prevent table locks from occurring unintentionally during report execution since all the transactions are committed after they are executed without additional commit commands.

Why are tables locked in MySQL?

MySQL also allows table locking to prevent it from unauthorized modification into the same table during a specific period. A session in MySQL can acquire or release locks on the table only for itself. Therefore, one session cannot acquire or release table locks for other sessions.

What is table locking in database?

The LOCK TABLE statement allows you to explicitly acquire a shared or exclusive table lock on the specified table. The table lock lasts until the end of the current transaction. To lock a table, you must either be the database owner or the table owner.


2 Answers

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.

like image 164
Jorge Muñoz Avatar answered Sep 27 '22 23:09

Jorge Muñoz


It seems as if the problem might lie in the fact that InnoDB indexes are weird.

The primary key (clustered) is i and there would be a rowid associated with it.

The unique key on j (nonclustered) has the rowid of i associated with the value of j in the index.

Doing a DELETE followed by an INSERT on the same key value for i should produce an upcoming different rowid for the primary key (clustered) and, likewise, an upcoming different rowid to associate with the value of j (nonclustered).

This would require some bizarre internal locking within MVCC mechanism.

You may need to change your Transaction Isolation Level to Allow Dirty Reads (i.e., not have repeatable reads)

Play some games with tx_isolation variable within a session
Try READ_COMMITTED and READ_UNCOMMITTED

Click here to see syntax for setting Isolation Level in a Session
Click here to see how there was once a bug concerning this within a Session and the warning on how to use it carefully

Otherwise, just permamnently set the following in /etc/my.cnf (Example)

[mysqld]
transaction_isolation=read-committed

Give it a try !!!

like image 23
RolandoMySQLDBA Avatar answered Sep 27 '22 22:09

RolandoMySQLDBA