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.
> (...) not making it a single transaction is basically saying you don't care about the stale data problem.

That's why I suggested a new type of transaction could be created. When you are storing logs, for example, it doesn't make difference storing A,B or B,A.

> 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.

It is just non-intuitive the default type of transaction of a DBMS could cause deadlocks so often and require retry mechanisms at application level, while no other DBMS require so.

Making IMMEDIATE transactions the default would make SQLite much easily adoptable. Any quick search about SQLite brings a lot of results of people saying it is not good, just because of the constant deadlocks (which could be avoided using IMMEDIATE transactions).

Also, libraries and tools that "standardize" commands for different DBMS systems would not need modify the "BEGIN" statement to "BEGIN IMMEDIATE" specifically for SQLite, causing even less friction in SQLite adoption.

Alternatively, there could be a SQLite specific statement (preferably "IMMEDIATE") to acquire a lock (with automatic retries, up to the defined timeout - not failing immediately) after "BEGIN" command has been sent. Example:

```
BEGIN;
IMMEDIATE; <- acquire write lock (as using BEGIN IMMEDIATE)
SELECT ...;
UPDATE ...;
COMMIT;
```
i.e.: BEGIN; + IMMEDIATE; would behave the same as the single statement BEGIN IMMEDIATE;

Yet, the statement could be sent outside a transaction to make all further transactions to have that "mode" automatically.

```
IMMEDIATE; <-- set default transaction mode
BEGIN; <-- immediate transaction
SELECT ...;
UPDATE ...;
COMMIT;
BEGIN; <-- immediate transaction
SELECT ...;
UPDATE ...;
COMMIT;
DEFERRED; <-- set default transaction mode
BEGIN; <-- deferred transaction;
SELECT ...;
UPDATE ...;
COMMIT;
```