SQLite Forum

[SOLVED] Getting SQLITE_LOCKED where SQLITE_BUSY is expected

Documentation for `SQLITE_LOCKED` [says](https://www.sqlite.org/rescode.html#locked) that it can happen either because of a conflict inside the current connection, or because of someone who uses the same shared cache. Similarly, documentation for transactions [states](https://www.sqlite.org/lang_transaction.html#read_transactions_versus_write_transactions) (emphasis mine):

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

However, I managed to run into a situation where an attempt to upgrade to a write transaction results in `SQLITE_LOCKED` rather than `SQLITE_BUSY`.

To reproduce this, we'll need two SQLite CLI tools running in the same directory (I changed their prompts to clarify which is which).

First, we initialize a database in WAL mode:

first> .open test.db
first> pragma journal_mode='wal';
first> create table test (id integer primary key, note text);
first> insert into test(note) values ('hello'), ('world');

We open the same database in the second shell:

second> .open test.db

Back in the first shell, we start a transaction which obtains a read lock:

first> begin transaction;
first> select * from test;

In the second shell, we modify the table:

second> insert into test(note) values ('foo');

Now in the first one, we try to modify the table as well, but get `SQLITE_LOCKED`:

first> update test set note = 'bar' where note = 'hello';
Error: database is locked

This was a bit surprising, and the docs didn't clarify the situation because they insist that `SQLITE_LOCKED` can only happen because of shared cache or something on the same connection ­— neither of which applies to the scenario described above.

SQLite's behaviour is correct though. `SQLITE_BUSY` would imply that I can fix the first shell by waiting a bit, but this clearly won't work; I have to restart the transaction to get an up-to-date view of the database, hence `SQLITE_LOCKED`.

It'd be nice if docs for `SQLITE_LOCKED` and transactions mentioned this possibility.

(As usual with FLOSS, I only get a chance to talk to developers when something isn't perfect, but: thank you very much for creating, maintaining, and sharing SQLite with the rest of the world!)