Ali Rizvi wrote:
> My investigation shows me that the following
> happens, but I am at a loss that why does the
> deadlock occur? A single row is involved and only
> three types of queries are involved:
> 1 - SELECT for UPDATE
> 2 - SELECT
> 3 - UPDATE
>
> Process A already holds an exclusive lock an a row
> (by doing SELECT for UPDATE on that row)
>
> At the same 'time' the following happens:
> - Process B tries to get exclusive lock (by doing
> SELECT for UPDATE) of the same row and waits for
> the lock
> - Process A runs an UPDATE query on its locked
> resource
>
> Deadlock occurs!
Actually, this is not what is happening, or at least not according to the status output below. What is actually happening is the UPDATE transaction is waiting for a separate lock to be released, and the SELECT ... FOR UPDATE is waiting for the lock from the UPDATE transaction. It's the reverse of what you state above.
It's tough to tell why the deadlock occurs, because we can't see the whole UPDATE statement. However, usually this occurs because another SELECT ... FOR UPDATE statement is occuring on a similar cluster key value or range of values, or a trigger has been created on the updated table, causing various issues.
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com