Yes, the deadlock is possible. Exactly this scenario is described in the documentation. BEGIN IMMEDIATE was invented in large part to prevent this situation, if I recall correctly. Consider using it for a transaction that starts as a reader but expects to upgrade to a writer later.

I have been bitten by this one many times it seems. What is the point of DEFERRED then? Are there cases when busy handlers are not called (and dead lock continues indefinitely?)

If DEFERRED causes dead locks so easily, don't you think it should be written in the docs?

Thank you for your answers.

Does upgrading a DEFERRED to an IMMEDIATE prevent deadlocks in all cases?

ensure that databases are always attached in the same order

Or that SQLite uses an arbitrary deterministic order, independent from the attach order. But it's probably too late for that?

Kinda like https://en.cppreference.com/w/cpp/thread/lock does to lock several mutexes.

Thank you all, really interested to the answer to those questions.

Although I think I will do my best to avoid multiple ATTACH in the future from now.

SQLite detects this deadlock and the process that is trying to update its lock gets an IMMEDIATE SQLITE_BUSY as SQLite knows that its wait can't succeed without someone aborting their action.

Thank you, everyone, for your answers and links to the relevant documentation!

Given these answers, may I suggest adding something like the following paragraph to the "DEFERRED, IMMEDIATE, and EXCLUSIVE transactions" section of https://www.sqlite.org/lang_transaction.html?

Lock contention that cannot be resolved via a busy timeout of any length can occur if a DEFERRED transaction starts with a read statement and later executes a write statement, because another process might be executing a write statement in between these two, in which case the first process has a read lock that can't be upgraded to a write lock until the second process completes its write, but that process can't complete its write until the first process releases its read lock. In this case, SQLite detects this condition and immediately returns SQLITE_BUSY to the first process, but applications that don't have a graceful way to handle a SQLITE_BUSY for such cases should use BEGIN IMMEDIATE for transactions that start with a read and later execute a write.

Is that not already covered by the paragraph in section 2.1?

If a write statement occurs while a read transaction is active, then the read transaction is upgraded to a write transaction if possible. If some other database connection has already modified the database or is already in the process of modifying the database, then upgrading to a write transaction is not possible and the write statement will fail with SQLITE_BUSY.

It could perhaps be clarified that the busy handler/busy timeout is not involved in this scenario, since this process will never be able to proceed until it relinquishes its read lock to allow the writing process to finish its turn.

This raises a question: how is the calling code supposed to distinguish a SQLITE_BUSY that can be dealt with retries, from one that can't be dealt with in that way?

Should a different return value be provided, to let the caller know which is which?

The calling code is supposed to remember whether or not it started a deferred transaction.

The calling code has no idea. However the programmer who wrote the code ought to know what it is doing.

I don't think it's a bad idea, although at this point it would have to be implemented as an extended error code to avoid backwards compatibility issues.

Although I think most use cases are satisfied by setting the busy timeout to an appropriate value and letting it handle the retries. That way the application only sees SQLITE_BUSY when (a) it is impossible to proceed or (b) retry attempts have exceeded the timeout. In what circumstances would you want to treat SQLITE_BUSY differently under this setup? If the answer is "keep retrying when it can proceed" then the solution is to increase the busy timeout. Eventually you presumably hit a point where the timeout becomes unpalatable and thus once SQLITE_BUSY is returned it is time to give up.

For more subtle logic an application can install its own busy handler callback instead of using a simple timeout, and so there is another perspective here which crowns the busy handler as the canonical API for announcing and handling "retriable" lock contention. From this perspective it may actually make the API more confusing to add an additional error code as this would provide a second (less-optimal) way to do the same thing.

(edit: the crucial subtlety here is that the busy handler is never invoked in the case of an SQLITE_BUSY which cannot be dealt with by retrying)

It could perhaps be clarified that the busy handler/busy timeout is not involved in this scenario

Yes, this is what I was confused about until it got answered/clarified in this thread. When I initially read both sections 2.1 and 2.2, what I saw was:

[For BEGIN DEFERRED, subsequent] write statements will upgrade the transaction to a write transaction if possible, or return SQLITE_BUSY.

...

BEGIN IMMEDIATE might fail with SQLITE_BUSY if another write transaction is already active on another database connection.

So I thought that the only difference was whether you preferred to handle a SQLITE_BUSY during the BEGIN statement, or whether you preferred to handle it during the first write statement. I did not understand, until thinking it through a bunch more and posting this thread, that it's not just about when the SQLITE_BUSY is returned, but about whether it can get resolved via a busy handler/timeout.

Is this the correct understanding of DEFERRED transaction behavior? And if so, does this basically mean that application code shouldn't use deferred transactions for transactions that involve a read operation followed by application code followed by a write operation?

It's a correct understanding, except that one of the transactions gets an error immediately as it is able to determine that there's no way that it will ever be able to proceed to write with the view it has of the DB at the start of the transaction it began.

It means that if you (the application programmer) have a transaction that starts out doing reads but then possibly needs to do a write, you should to use an immediate transaction for that anyway. Like that, you will wait for the troublesome lock to clear. Getting this wrong is a particularly insidious bug when you're doing the same upgrading transactions on multiple threads at once, as some threads will succeed and others will fail arbitrarily and it is hard to get good visibility of why this is so. Doing better than this would require a much more complex lock implementation inside SQLite (row level locking without a server process to mediate is going to be nasty to code up in a small space).

Alternatively, you can try restarting the transaction that failed (presumably in immediate mode so it waits until the other thread/process clears). Assuming you designed your code to be able to do that...