CODEWITHSUNDEEP
mysqllockingdeadlock
Marcus Adams
Marcus Adams

Reputation: 53850

Two duplicate delete queries using primary key causing deadlock

I don't understand how two duplicate queries that each delete a single row against a single table using the primary key could have deadlocked. Can anyone explain?

It seems to me like one of the transactions should have gotten the lock and the other one would have to wait.

Here's the deadlock report, with the queries:

Fri Jun 01 2012 13:50:23
*** (1) TRANSACTION:
TRANSACTION 3 1439005348, ACTIVE 0 sec, process no 22419, OS thread id 1166235968 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368
MySQL thread id 125597624, query id 3426379709 node3-int 10.5.1.119 application-devel updating
DELETE FROM `SessData` WHERE `SessKey` = '87EDF1479A275557AC8280DCA78AB886'
AND `Name` = 'CurrentRequestURL'

*** (2) TRANSACTION:
TRANSACTION 3 1439005340, ACTIVE 0 sec, process no 22419, OS thread id 1234073920 starting index read, thread declared inside InnoDB 0
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1216
MySQL thread id 125597622, query id 3426379705 node2-int 10.5.1.118 application-devel updating
DELETE FROM `SessData` WHERE `SessKey` = '87EDF1479A275557AC8280DCA78AB886'
AND `Name` = 'CurrentRequestURL'

*** WE ROLL BACK TRANSACTION (2)

Here's the schema for the table:

CREATE TABLE  `application`.`SessData` (
  `SessKey` varchar(255) NOT NULL default '',
  `Name` varchar(255) NOT NULL default '',
  `Value` varchar(255) default NULL,
  PRIMARY KEY  (`SessKey`,`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

A few other details:

MySQL version: 4.1.21
Isolation level: REPEATABLE-READ
Character set on the the above columns: latin1

Upvotes: 9

Views: 2195

Answers (3)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

I remember about a year ago helping someone troubleshoot a similar deadlock situation

Not to be overlooked is the fact that InnoDB deadlocks can be caused by SELECT statements under specific circumstances : https://dba.stackexchange.com/questions/4469/are-innodb-deadlocks-exclusive-to-insert-update-delete/4470#4470 (August 8, 2011)

Please look at your SHOW INNODB ENGINE STATUS\G. Since you are using MySQL 4.1, the info is not as complete as it should in order to reveal a problem.

Notwithstanding, what is going on here? You are essentially locking the Clustered Index (also called the gen_clust_index). Two locks on the same exact row locks the same row and its gen_clust_index entry in the Clustered Index.

Only one can be locked exclusively. The other would be locked as exclusive but waiting. Of course, the last exclusive lock had to win. If both transactions timeout at the same time, this must have occurred in 50 seconds (default for innodb_lock_wait_timeout) for one or both.

So, who get's rolled back? According to your MySQL 4.1 SHOW INNODB ENGINE STATUS\G, TRANSACTION (2) would bite the dust and rollback because TRANSACTION (1) got a hold of the cluster key entry first.

Upvotes: 1

Old Pro
Old Pro

Reputation: 25547

You are using MySQL version 4.1.21. 4.1 is past its end-of-life and 4.1.21 isn't even the latest 4.1 version. (Extended support for MySQL 4.1 ended on December 31, 2009.) You should upgrade to at least 5.0.96, though you might as well come fully up-to-date to 5.5.25. Failing that, an upgrade to 4.1.22 would be the minimum you could do, though that probably won't fix your problem.

If you read the last example in the MySQL 4.1 documentation you see how this deadlock could occur if the row being deleted had previously been selected with a shared lock earlier in the transaction. Likewise you could have acquired shared locks if there are foreign key constraints involved. The general problem is:

A acquires a shared lock on x

B waits for an exclusive lock on x. It has to wait because of A's lock.

A waits for an exclusive lock on x. It has to wait because B is ahead of it in the queue for the exclusive lock.

The way InnoDB handles locks, it will not upgrade A's shared lock to exclusive while B is waiting for the same exclusive lock, so this is a deadlock.

Alternarely, you may be hitting a bug when the two statements are both trying to delete a non-existent row (possibly just deleted by an immediately preceding third duplicate delete). Possibly related to:

Upvotes: 4

eggyal
eggyal

Reputation: 125865

As the manual states:

DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

Elsewhere, it explains:

Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record.

Since there are more than two locks involved, one connection might obtain one such lock whilst the other connection obtains the other; they will then be deadlocked waiting on eachother for the release of the lock which they do not hold.

Whilst you can disable gap locking by using the READ COMMITTED isolation level, that does expose you to phantom rows. You would be better to detect and reissue transactions that fail in the event of deadlock (as it happens in this case, the transaction that succeeds will delete the record and so the rolled-back transaction will not need to be reissued).

Upvotes: 1

Related Questions