ft55 (4/29/2013)
Thank you for all the comments so far.Could you expand on what you mean by reordering the transaction and how i might relate that to a select statement
I also noticed a blog that mentioned using try and catch around the statement so that if the statement failed due to a deadlock error it would automatically try again which sound quite neat ?
Thanks
Frant101
Ok...
Transaction 1 gets a lock on a given resource, let's called A.
Transaction 2, acquires a lock on resource B.
Now, transaction 1 also requires a lock on B, but that's not possible, because transaction 2 still holds a lock on same resource. At the same time, Transaction 2 needs a lock on A, but because 1 is still holding same lock, waiting for 2, none of those can't finish.
We have a deadlock here.
You fix above, if you make transaction 1 short enough, so by the time if holds the lock on B, transaction 2 has not started yet. That way, transaction 1 can complete without any problems. Then later transaction 2 can be called and will complete too, because nothing is holding a lock on A, which at some point will be needed by transaction 2.
It's purely a logic problem.