Re: Deadlock apparently on a single resource: How to avoid?

[Jan Lindström]

Ali,

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 14340 n bits 120 index `PRIMARY` of table `TS/TICKET` trx id 0 248835204 lock_mode X waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;

Information about SQL-query witch has requested this lock is not printed here because that SQL-query has already executed and is not active any more. At the moment MySQL does not store history of the executed SQL-queries in the transaction and what locks these SQL-queries have taken. Note that this feature is planned but I'm not sure when it is scheduled.

InnoDB currently prints only the direct causes of deadlock not indirect. In your case SELECT... FOR UPDATE is part of the history witch caused the deadlock but deadlock is formed between two UPDATES. Note that SELECT FOR UPDATE does hold a locks even in your case, this can be seen from the output showing how many lock structs trasactions is holding.

Jan Lindström
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL