What are some examples of circumstances when waiting and retrying a lock won't help?
Consider two processes, A and B. Lock state is in [BRACKETS].
(A) does a BEGIN TRANSACTION
(B) does a BEGIN TRANSACTION
(A) does a SELECT [SHARED]
(B) does an INSERT or UPDATE [RESERVED]
At this point, (B) will be unable to END TRANSACTION successfully until (A) does an END TRANSACTION. Now, consider:
(A) does an INSERT or UPDATE
Now, (A) will attempt to upgrade its lock to (RESERVED). It will fail because (B) already has a RESERVED lock. At the same time, (B) cannot end the transaction because it will be unable to transition the RESERVED lock to the EXCLUSIVE lock required for writing. If (A) and (B) decide to loop waiting for a lock at this point, they will deadlock waiting on the other. The only way to resolve is for one or the other process to ABORT or for (A) to do an END TRANSACTION (which will work because the INSERT/UPDATE attempt on (A) failed to advance the lock state).
b.bum
