A: begin; select; insert; B: begin; select; B: insert; (busy, so waits and polls occasionally) A: commit; (busy, so waits and polls occasionally)
Is there any plan to allow detection or prevention of this case? I see three solutions.
1. Tell users to only insert or update after BEGIN IMMEDIATE. That will prevent this deadlock. But prevents might break a lot of uses.
2. Add something like sqlite3_safe_write(), which will upgrade to a RESERVED lock (acts like an insert or update without actually changing anything). If it works, return success. Otherwise, decide if we're BUSY or DEADLOCKED:
If the caller is UNLOCKED (only happens if it's the first use of a deferred transaction), then we're BUSY since there's no deadlock possible.
If the caller is SHARED and there is no write pending (see below), return BUSY since we can't deadlock.
If the caller is SHARED and there is a write pending, then return DEADLOCKED.
If we return BUSY for any reason, this function would need to atomically set a write pending flag of some sort, to warn off future writers.
Any attempt to modify the database without an explicit sqlite3_safe_write() would call sqlite3_safe_write() first. The reason for allowing the explicit call is so that a user planning a complex calculation between a select and an insert could explicitly upgrade the lock first and not bother with the calculation if the insert will just deadlock later.
All the blocking (polling) calls would then simply fail on DEADLOCKED.
Is this correct and sensible?
--Andy
