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

[Ali Rizvi]

Jay Pipes wrote:
> It's tough to tell why the deadlock occurs, because we can't see the whole UPDATE statement.

I have looked at the process that runs the UPDATE query and I can tell you that the UPDATE query was updating the row with TIKNUM='100000EF8V' , the same row for which SELECT ... for UPDATE is waiting.

The SELECT ... FOR UPDATE transaction is waiting for the lock that is HELD by the UPDATE transaction, as you have also noted. But please note that the SELECT ... FOR UPDATE transaction does NOT hold any locks as shown in the innodb status output.
What I don't understand is that what 'separate lock' is the UPDATE transaction waiting for? And that who is holding that 'separate lock'? The output shows that the SELECT .. FOR UPDATE transaction does not hold that lock or any lock.

By the 'separate lock' I am referring to the following as shown in the output:

*** (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 ;;


If this lock is involved in the deadlock then why doesn't the innodb status output show the transaction that is holding this lock? And even if there is another transaction holding this lock, how is it involved in the deadlock that occured? Are you suggesting some chain of three transactions involved in holding each other's locks? But still you see the SELECT ... FOR UPDATE transaction does not hold any lock at all.

I have an understanding that for a deadlock to occur transaction T1 should hold lock on resource R1 and wait for resource R2, and transaction T2 should hold lock on resource R2 and wait for resource R1. In the given case one of the transactions does not hold any lock at all, and the other transaction is waiting for some resource that is not mentioned anywhere in the output as locked.

Please comment,
Thanks,
Ali Rizvi
Kolachi Advanced Technologies