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)
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
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.
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.
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.
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.
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.
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 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 !!!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With