I'm trying to decide if I should use BEGIN IMMEDIATE transactions in my project.

From https://kerkour.com/sqlite-for-servers

By default, SQLite starts transactions in DEFERRED mode: they are considered read only. They are upgraded to a write transaction that requires a database lock in-flight, when query containing a write/update/delete statement is issued.

The problem is that by upgrading a transaction after it has started, SQLite will immediately return a SQLITE_BUSY error without respecting the busy_timeout previously mentioned, if the database is already locked by another connection.

This is why you should start your transactions with BEGIN IMMEDIATE instead of only BEGIN. If the database is locked when the transaction starts, SQLite will respect busy_timeout.

I want to see this for myself. I've been trying and failing to write Python code that can trigger a "database is locked" / SQLITE_BUSY error which stops being an error when I switch to a BEGIN IMMEDIATELY transaction.

Is the explanation I quoted here accurate? If so, can anyone provide tips on triggering the described condition so I can prove to myself that the problem can be fixed in this way?

Open two connections.

Connection A does BEGIN then SELECT * FROM table, then sleeps for 30 seconds.

While connection A is sleeping, connection B does INSERT INTO table VALUES (...). Connection B blocks waiting for connection A's read transaction to finish.

While connection B is blocked, connection A does its own INSERT INTO table VALUES (...). It immediately gets a SQLITE_BUSY error and the connection B succeeds.

By default, BEGIN doesn't do anything to the database. It just makes a note, associated with its connection, that if you execute a SQL command that requires a transaction, instead of creating a transaction just for that one command, SQL should start a multi-statement transaction which requires COMMIT or ROLLBACK.

The effect of this is that you can issue BEGIN, then read a data file or wait for user input, before you actually need the transaction, and SQL won't keep the database locked for that time.

Obviously, after the BEGIN, but before the operation which requires the database to be locked, another connection might sneak in and lock the database and still have it locked when your transaction should start. To avoid this, use BEGIN IMMEDIATE, which means you'll never get that situation, but also that you are keeping the database locked before you actually need it, which will annoy other programs or users.

To test it you need two simultaneous connections to the database as Nuno Cruces suggests. It can't be tested using a single connection. The easiest way to do this is to have your Python connection running at the same time as you're connecting to the same database using the SQLite CLI. And to use something like sleep() to give yourself time to react. You should test both ways around: Python being locked out by the CLI, and the CLI being locked out by Python.

Simon, I was reading your post at https://simonwillison.net/2024/Mar/31/optimizing-sqlite-for-servers/ and the one you referenced at https://kerkour.com/sqlite-for-servers.

I appreciate to find this thread and you investigating for yourself before taking some advice for granted!

I decided to do the same and replicate the steps proposed in this thread. For what it's worth, I'm particularly interested on the behavior in WAL mode.

If I understood well, and confirming my understanding of BEGIN IMMEDIATE based on the official docs, I think it immediately starts a write transaction even if you never issue any writes in that transaction.

I used two sqlite3 shell instances to open the same file immediate.db with journal mode WAL. Issued "BEGIN IMMEDIATE;" from one shell, then issued the same command on the second shell getting "Runtime error: database is locked (5)".

If my understanding is correct, then I fail to see the point in indiscriminately replacing all use of BEGIN with BEGIN IMMEDIATE, and wrapping every database interaction with BEGIN IMMEDIATE, as it seems to prevent concurrent reads & concurrent reads+write.

BEGIN IMMEDIATE is selectively useful if I want to start a transaction that will include a write operation after one or more read operations, so to avoid a BUSY_ERROR in the middle of the transaction when upgrading the read lock into a write lock when another transaction might be holding the write lock.

Does that match with your own findings?

I've been trying and failing to write Python code that can trigger a "database is locked" / SQLITE_BUSY error which stops being an error when I switch to a BEGIN IMMEDIATELY transaction.

1. You need two threads, each with their own connection. (This requires multiple simultaneous transactions, which requires multiple connections.)
2. You need the threads to be each running a query that upgrades the transaction, such as by doing an insert conditionally while in the middle of iterating over a result set. There are other ways to achieve this, but that's the easy one (and the case that's actually fairly realistic if you're not careful).
3. You need some sort of timing/synchronization control so that each thread starts its reading at a similar time and then tries to upgrade the transaction at about the same time. OK, you can fire things off and hope, but getting some synching in there should help you hit it reliably.
4. You need to set SQLite to time out on a failure to upgrade a transaction. I don't know if that happens by default now, but it didn't in how things were configured for me a few years back.

In my case, I was running a small user auth database for a webapp (which needed to update the DB on successful login) and had exported bcrypt as a function to SQLite. It worked very well for single clients, but when there were multiple clients there were occasional mysterious hangs (this was a multithreaded Java app, but the language isn't important). The problem was I had things taking a long time in the read stage (because of the bcrypt; don't do that!) and the connection upgrades were then trying to run past each other, which was the hang (as I wasn't getting SQLITE_BUSY at that point, for whatever reason). I solved it by making sure that transactions with writes in my code had to have been commenced with BEGIN IMMEDIATE and by changing the architecture of the authentication code to not hold any transaction at all while calling bcrypt. I dare say you could achieve the same thing with multiple processes instead, but the coordination to reliably hang would be trickier.

Upgrades are convenient but really dangerous when there are multiple writers about. The fix for it would involve the scope of transaction locks getting smaller; when they're per-DB, failures are quite easy to trigger, whereas when they're per-row then you rarely get them. Per-table would be between those extremes, but still pretty likely to fail in real scenarios.

The application concerned got switched to MySQL a couple of years ago (which now seems to support more standard SQL than it used to). I'm not sure that that was a good design decision, but I wasn't going to spend time arguing it.