SQLite Forum

New type of transaction (or a new behavior for DEFERRED transactions)
Login
The problem with not giving an Error on the detected deadlock is that inconsistent results could happen. If transaction A reads some values, computes some changes based on those values and then goes to write them, but finds that someone else is doing a write, then because that write might change the values that A has read, the values A computed might not be right after B finishes its write, thus A does need to redo its work.

In a more complicated world, SQLite could keep track of every record that a read transaction has read, and only give the busy if a write transaction changes some of that data, but that goes away from 'lite', but is what some Database systems will do.

Note, with the rules SQLite uses, it is impossible for an application to read 'stale' data and write back results based on it as long as the full operation is part of a single Transaction, as any write operation that is in progress or finishes after the read transaction started (and thus might have changed values) makes SQLite return the error for trying to update the read to a write transaction. And not making it a single transaction is basically saying you don't care about the stale data problem.

Making IMMEDIATE transactions the default would reduce the ability for a read-only transaction to be able to start if there is a write transaction in progress. You of course always have the ability to just make your own application just issue BEGIN IMMEDIATE for every transaction, or every transaction that just might want to write so you don't need to worry about getting the deadlock BUSY answer.