Menu
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
594
Because both transactions are waiting for a resource to become available, neither ever release the locks it holds. A deadlock can occur when transactions lock rows in multiple tables (through statements such as UPDATE or SELECT ... FOR UPDATE ), but in the opposite order.
In a SQL database, a record is typically called a "row". The introduction of granular (subset) locks creates the possibility for a situation called deadlock. Deadlock is possible when incremental locking (locking one entity, then locking one or more additional entities) is used.
Update lock (U) is used to avoid deadlocks. Unlike the Exclusive lock, the Update lock places a Shared lock on a resource that already has another shared lock on it.
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:
96
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
