SQLite Forum

[SOLVED] Getting SQLITE_LOCKED where SQLITE_BUSY is expected
Login

[SOLVED] Getting SQLITE_LOCKED where SQLITE_BUSY is expected

(1.1) By Alexander Batischev (Minoru) on 2021-10-26 16:24:59 edited from 1.0 [link] [source]

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

(2) By Keith Medcalf (kmedcalf) on 2021-10-25 23:06:01 in reply to 1.0 [link] [source]

Are you sure that the error code is not SQLITE_BUSY_SNAPSHOT, just being simplificated for display to the user by the application?

(3.2) By Keith Medcalf (kmedcalf) on 2021-10-26 00:45:16 edited from 3.1 in reply to 2 [link] [source]

Using a programming language to do it you get the following:

Python 3.9.7 (tags/v3.9.7:1016ef3, Aug 30 2021, 20:19:38) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> db = apsw.Connection('test.db')
>>> db.begin()
<newapsw.Cursor object at 0x000002BB8208EA00>
>>> for row in db.execute('select x from x'): print(row)
...
Row(x=5)
>>> try:
...  db.execute('update x set x=-1')
... except BaseException as e:
...  x = e
...
>>> x
BusyError('BusyError: database is locked')
>>> x.extendedresult
517

Note that the extended error code is 517 (SQLITE_BUSY_SNAPSHOT)

This is because an attempt is being made to update a non-top read-locked snapshot to a write lock, but this cannot be done because in order for a read locked snapshot to be upgraded to a write locked snapshot, it must be at the top of stack. However, another update was done in another connection, so the transaction is no longer able to be upgraded at the present time (and may or may not ever be able to be upgraded -- which case applies is up to the programmer to determine).

** NB: I have MonkeyPatched APSW to have added methods such as .begin, .execute, and .commit to the apsw Connection class **

(6) By Alexander Batischev (Minoru) on 2021-10-26 14:32:21 in reply to 2 [link] [source]

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!

(4) By Keith Medcalf (kmedcalf) on 2021-10-26 04:23:24 in reply to 1.0 [link] [source]

Perhaps the CLI application can be modified to display the extended error code as well as the text. It is displayed in some places, but not in others.

Instead of printing the error message as:

Error: <message text>

could it perhaps be displayed as:

Error: <message text> (<extended error code>)

Also, could the word Error: be augmented to indicate WHERE the error was detected: Error during Prepare: or Error during Step: if it can be determined whether the error was detected at PREPARE or STEP time (though it is noteworthy that errors can already be distinguished because there are very few errors that can occur at BOTH places rather than only one).

That is instead of the CLI printing the message Error: database is locked in the above circumstance, perhaps the CLI could print the message Error during Step: database is locked (517).

This would make the CLI more useful as a debugging tool.

(5.1) By Larry Brasfield (larrybr) on 2021-10-26 16:18:19 edited from 5.0 in reply to 4 [source]

Perhaps the CLI application can be modified to display the extended error code as well as the text. ...

This seems like a good idea to me, and fairly simple to implement.
(Edit resulting from discussion with dev team:)
Although it's a feature change, it is trivial enough to be included in v3.37 (unless my perception of triviality is way off.)

Also, could the word Error: be augmented to indicate WHERE the error was detected: Error during Prepare: or Error during Step: if it can be determined whether the error was detected at PREPARE or STEP time (though it is noteworthy that errors can already be distinguished because there are very few errors that can occur at BOTH places rather than only one).

(Correction via edit regarding silence upon stepping error:)

At present, any error returned from sqlite3_step() is treated as the end of stepping, then reported like others. Given the potential locking issues raised in this thread, indicating this also seems worthwhile.

(7) By Keith Medcalf (kmedcalf) on 2021-10-28 06:00:40 in reply to 5.1 [link] [source]