SQLite Forum

Timeline
Login

2 forum posts by user crawshaw

2021-09-27
16:02 Reply: PRAGMA wal_checkpoint(RESTART) unexpectedly returning SQLITE_BUSY immediately (artifact: 6a66501e4d user: crawshaw)

That's it. Setting PRAGMA wal_autocheckpoint=0; on every connection resolves the issue and lets PRAGMA wal_checkpoint(RESTART); succeed reliably. (I did see a single SQLITE_BUSY in my load test, but there is a lot of code involved and I am investigating if I accidentally created a connection inappropriately.)

Turns out this is well-documented, I somehow missed it.

All calls obtain an exclusive "checkpoint" lock on the database file. If any other process is running a checkpoint operation at the same time, the lock cannot be obtained and SQLITE_BUSY is returned. Even if there is a busy-handler configured, it will not be invoked in this case.

Thank you Dan!

2021-09-26
19:33 Post: PRAGMA wal_checkpoint(RESTART) unexpectedly returning SQLITE_BUSY immediately (artifact: b1d109d934 user: crawshaw)

Hello,

I have a busy, very concurrent SQLite database: 36 connections open from the same process, about 1000 writes per second. As is apparently expected, the WAL file is growing without bound. To counter this, I am attempting to deliberately insert a periodic "stop-the-world" style event by calling:

PRAGMA wal_checkpoint(RESTART);

As I understand it, this will grab an exclusive lock, invoking the busy handler.

All of these connections are configured with a busy_timeout of 10 seconds. But the PRAGMA wal_checkpoint(RESTART) returns busy within a microsecond. Similarly, sqlite3_wal_checkpoint_v2 returns SQLITE_BUSY within a microsecond.

Am I misunderstanding the documentation of sqlite3_wal_checkpoint?

Thank you,

David