Comment 1 for bug 1323235

Revision history for this message
lisheng.liu (lisheng-liu) wrote :

I found some other customer get similar problem by 3 threads running in parallel to delete same row in high concurrency.
and the reason is the second and third thread both get into no gap lock wait before this record not be marked to delate by thread , not X lock wait.
Then after thread1 commit thread 2 and 3 get gap lock but because of index id unique index it need none gap lock then they trying to request X lock which caused deadlock.

I found the symptom slimiar in deadlock info, so just for reference while you analyze checking source code, hope it could help.

---------------------------
LATEST DETECTED DEADLOCK
------------------------
140123 12:20:50
*** (1) TRANSACTION:
TRANSACTION 2E10, ACTIVE 5325 sec 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 3, OS thread handle 0x1008, query id 43 192.168.xx.x username upd
ating
delete from abcdefg WHERE abc= 'vars' and def= 'vars' and ghi= '2012-12-19 00:00:00' and jkl= '2012-12-20 00:00:00';
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 23415 n bits 528 index `uniqeindex` of table
`deadlock`.`abcdefg` trx id 2E10 lock_mode X locks rec but not gap waiti
ng
Record lock, heap no 167 PHYSICAL RECORD: n_fields 4; compact format;

*** (2) TRANSACTION:
TRANSACTION 2E0E, ACTIVE 5325 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 1, OS thread handle 0x1190, query id 41 192.168.xx.xx username upd
ating
delete from abcdefg WHERE abc= 'vars' and def= 'vars' and ghi= '2012-12-19 00:00:00' and jkl= '2012-12-20 00:00:00';
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 23415 n bits 528 index `uniqeindex` of table
`deadlock`.`abcdefg` trx id 2E0E lock_mode X locks rec but not gap
Record lock, heap no 167 PHYSICAL RECORD: n_fields 4; compact format;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 23415 n bits 528 index `uniqeindex` of table
`deadlock`.`abcdefg` trx id 2E0E lock_mode X waiting
Record lock, heap no 167 PHYSICAL RECORD: n_fields 4; compact format;
*** WE ROLL BACK TRANSACTION (1)
---------------------------