I've found a very confusing deadlock situation that I need help to understand.
There are two transactions going on:
(2) holds a lock for the query delete from myTable where id = NAME_CONST('p_id',10000)
. This is a lock by PRIMARY KEY although not the full key but a range. It looks like this is a full write lock to me when it says lock_mode X locks rec but not gap
.
(1) is waiting for this same lock, also for the query delete from myTable where id = NAME_CONST('p_id',10000)
.
(2) is also trying go get this lock and MySQL detects a deadlock.
What I can't understand is why (2) has to acquire the lock again as it already holds it and it's a write lock (lock_mode X) in all cases.
It also looks like it's for the exact same query.
Here is the table definition
create myTable (
id int unsigned not null,
value1 char(8) not null,
value2 int unsigned,
primary key (id, value1)
);
and here is the information from SHOW ENGINE INNODB STATUS\G
------------------------
LATEST DETECTED DEADLOCK
------------------------
130313 14:46:28
*** (1) TRANSACTION:
TRANSACTION 75ACB8A3, ACTIVE 0 sec, process no 6110, OS thread id 139973945382656 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 5154970, query id 5201313618 192.168.0.2 user updating
delete from myTable where id = NAME_CONST('p_id',10000)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 22371 page no 1598 n bits 104 index `PRIMARY` of table `db`.`myTable` trx id 75ACB8A3 lock_mode X waiting
Record lock, heap no 32 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 0005af3a; asc :;;
1: len 8; hex 2020202020202020; asc ;;
2: len 6; hex 000075acb890; asc u ;;
3: len 7; hex ea0000020d011e; asc ;;
4: len 4; hex 00000065; asc e;;
*** (2) TRANSACTION:
TRANSACTION 75ACB890, ACTIVE 0 sec, process no 6110, OS thread id 139973957895936 starting index read
mysql tables in use 1, locked 1
7 lock struct(s), hea
p size 1248, 6 row lock(s), undo log entries 4
MySQL thread id 5155967, query id 5201313625 192.168.0.1 user updating
delete from myTable where id = NAME_CONST('p_id',10000)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 22371 page no 1598 n bits 104 index `PRIMARY` of table `db`.`myTable` trx id 75ACB890 lock_mode X locks rec but not gap
Record lock, heap no 32 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 0005af3a; asc :;;
1: len 8; hex 2020202020202020; asc ;;
2: len 6; hex 000075acb890; asc u ;;
3: len 7; hex ea0000020d011e; asc ;;
4: len 4; hex 00000065; asc e;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 22371 page no 1598 n bits 104 index `PRIMARY` of table `db`.`myTable` trx id 75ACB890 lock_mode X waiting
Record lock, heap no 32 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 0005af3a; asc :;;
1: len 8; hex 2020202020202020; asc ;;
2: len 6; hex 000075acb890; asc u ;;
3: len 7; hex ea0000020d011e; asc ;;
4: len 4; hex 00000065; asc e;;
*** WE ROLL BACK TRANSACTION (1)
Deadlock happens when different concurrent transactions are unable to proceed because each one holds a lock that the other needs.
Deadlock frequency can sometimes be reduced by ensuring that all applications access their common data in the same order - meaning, for example, that they access (and therefore lock) rows in Table A, followed by Table B, followed by Table C, and so on.
A deadlock happens when multiple lock waits happen in such a manner that none of the users can do any further work. For example, the first user and second user both lock some data. Then each of them tries to access each other's locked data. There's a cycle in the locking: user A is waiting on B, and B is waiting on A.
It's not the same lock - the lock transaction 1 has is on the (index) record only and not the gap lock.
Here's what's happening:
I'm not entirely sure why Transaction 2 doesn't acquire a next-key lock immediately - perhaps the process of obtaining the record lock and then the gap lock isn't atomic (in the general sense of the word).
I think the issue is that you have a composite primary key (id, value1) but you are deleting from a range (specifying id only) - this requires gap locks. See http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html, in particular:
Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.)
Can you change your code so you specify the full primary key when deleting, i.e. id and value1?
Other options:
Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.
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