Maybe I'm a bit confused - wouldn't (A) first give up it's SHARED lock before trying to get the RESERVED lock, thus giving (B) a chance to get the EXCLUSIVE lock?
Not in a transaction.
The purpose of a transaction is to group together a series of operations for both efficiency's sake and to ensure integrity.
For example, you might do a...
BEGIN SELECT SELECT SELECT INSERT INSERT UPDATE END
Where the INSERTs and UPDATE are dependent upon the results of the SELECTs. By putting everything into a transaction, there cannot be a potentially catastrophic state change between the SELECTs and the INSERTs/UPDATE.
If someone else were allowed to grab an EXCLUSIVE between the SELECT and INSERT in the above example, then that EXCLUSIVE might be used to change the database in a fashion where the results of the SELECTs change, thus breaking assumptions made by INSERT or UPDATE.
I guess I should really upgrade to sqlite3, which uses PENDING locks to try and eliminate writer starvation.
Even with PENDING, you can still write code to cause a deadlock as I originally described.
b.bum
