SQLite Forum

What reading wal can result in `BusyError: database is locked`?

What reading wal can result in `BusyError: database is locked`?

(1) By 6kEs4Majrd on 2020-06-21 08:36:16 [link] [source]

I got the following error when I read the journal_mode (in python apsw).

    if c.execute('PRAGMA journal_mode;').fetchone()[0] != 'wal':
  File "src/cursor.c", line 1019, in APSWCursor_execute.sqlite3_prepare
  File "src/statementcache.c", line 386, in sqlite3_prepare
apsw.BusyError: BusyError: database is locked

Since I just read something from the database, why does it matter whether the database is locked or not? Thanks.

(2) By Keith Medcalf (kmedcalf) on 2020-06-21 15:34:41 in reply to 1 [link] [source]

Because if the database is locked, you cannot read it.

(3) By Simon Slavin (slavin) on 2020-06-21 22:29:48 in reply to 1 [source]

Suppose a row is being written while you're trying to read it. You might get one field from before the change and another from after the change.

Or suppose you are reading a selection of rows and you get some rows from before a change and others from after a change. You might get an inconsistent picture from half way through a change.

So a database must be locked against reads while it is being updated.

(4) By 6kEs4Majrd on 2020-06-21 22:42:57 in reply to 3 [link] [source]

I only add rows to the database when the database is updated in other processes. So the wal is not affected. In this case, is there a way to relieve this restriction so that I can read the state of wal?

(5) By Richard Hipp (drh) on 2020-06-21 22:51:27 in reply to 4 [link] [source]

Your problem is not precisely stated, so it is hard to know what is going on. Please know that in WAL mode, a connection should be able to read the database at any time, regardless of what other connections are doing with the database at the same moment.

There are some rare exceptions to the above. For example, if the system takes a power loss in the middle of a transaction, then the next time the database file is opened, it must be recovered. This recovery is completely automatic. However, while the first process to open the database is busy running recovery, no other processes will be able to access the database file.

There are other equally obscure exceptions.

Without knowing more about your situation, we cannot easily speculate on why you are having difficulty.