SQLite Forum

Timeline
Login

3 events by user Minoru

2021-10-26
16:24 Edit: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected artifact: c68cb87012 user: Minoru

Hello!

Documentation for SQLITE_LOCKED says 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 (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';
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;
1|hello
2|world

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!)

14:32 Reply: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected artifact: 92ab78f89d user: Minoru

You're totally right. My bad, I wasn't attentive enough; I just read "database is locked" and assumed it's SQLITE_LOCKED, even though my program reported the error code of 5 (which is SQLITE_BUSY).

So sorry to waste your time with this; you even took the pain of reproducing it in yet another way, with Python — thanks for that!

2021-10-25
22:27 Post: SOLVED Getting SQLITE_LOCKED where SQLITE_BUSY is expected artifact: bc0ddb7834 user: Minoru

Hello!

Documentation for SQLITE_LOCKED says 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 (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';
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;
1|hello
2|world

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!)