I build Canli Capital: free, open-source tools that check whether a backtest is real (deflated Sharpe, overfitting and data-snooping tests), plus SEC fundamentals for AI agents over MCP.
The concurrency test only covers a writer that has an open transaction, and there RESERVED and SHARED do coexist. The case that can differ is a writer that commits while .diskused is still reading. In rollback-journal mode a commit has to get EXCLUSIVE, which waits for every SHARED lock to drop, and the dbstat scan holds SHARED for as long as it walks pages. I reproduced the shape in Python: with a reader holding an open SELECT cursor, BEGIN IMMEDIATE; INSERT; COMMIT with a 0.2 s busy timeout fails with "database is locked" at the COMMIT, not at the BEGIN.
On your 20 MB file the scan takes about 25 ms so you'd never notice, but on a multi-GB database .diskused in rollback mode could stall a writer's commit for the length of the report. In WAL mode the reader doesn't block the writer, so did you try the commit-during-scan case in the WAL run, or only the open-transaction one?
Nope, only the open-transaction case, in both modes. Commit-during-scan never got tested, and you're right that it's the one that actually matters. RESERVED next to SHARED was always gonna be fine, I basically tested the case that couldn't fail.
So I just ran it, same shape as yours but with the reader holding an open cursor on dbstat itself, since that's what .diskused walks. 0.2s busy timeout on the writer:
rollback journal: BEGIN IMMEDIATE and the INSERT go through, COMMIT fails with "database is locked"
WAL: the whole thing commits fine
Heads up, that was on the Python sqlite3 module, which is 3.53.4 here, not 3.54. The locking rules didn't change between them and it's the same dbstat table .diskused reads, so I'd expect 3.54 to behave the same, but I haven't run the actual .diskused command against it.
So yeah, on a big database in rollback mode, running .diskused next to a live writer can hold up that writer's commits for as long as the report runs, or fail them outright if the busy timeout is shorter than that. "Nothing changed" in that section is too strong, I'll add the commit case and the WAL vs rollback split. Thanks for actually reproducing it instead of just guessing.
We're a place where coders share, stay up-to-date and grow their careers.
The concurrency test only covers a writer that has an open transaction, and there
RESERVEDandSHAREDdo coexist. The case that can differ is a writer that commits while.diskusedis still reading. In rollback-journal mode a commit has to getEXCLUSIVE, which waits for everySHAREDlock to drop, and the dbstat scan holdsSHAREDfor as long as it walks pages. I reproduced the shape in Python: with a reader holding an open SELECT cursor,BEGIN IMMEDIATE; INSERT; COMMITwith a 0.2 s busy timeout fails with "database is locked" at the COMMIT, not at the BEGIN.On your 20 MB file the scan takes about 25 ms so you'd never notice, but on a multi-GB database
.diskusedin rollback mode could stall a writer's commit for the length of the report. In WAL mode the reader doesn't block the writer, so did you try the commit-during-scan case in the WAL run, or only the open-transaction one?Nope, only the open-transaction case, in both modes. Commit-during-scan never got tested, and you're right that it's the one that actually matters. RESERVED next to SHARED was always gonna be fine, I basically tested the case that couldn't fail.
So I just ran it, same shape as yours but with the reader holding an open cursor on dbstat itself, since that's what .diskused walks. 0.2s busy timeout on the writer:
Heads up, that was on the Python sqlite3 module, which is 3.53.4 here, not 3.54. The locking rules didn't change between them and it's the same dbstat table .diskused reads, so I'd expect 3.54 to behave the same, but I haven't run the actual .diskused command against it.
So yeah, on a big database in rollback mode, running .diskused next to a live writer can hold up that writer's commits for as long as the report runs, or fail them outright if the busy timeout is shorter than that. "Nothing changed" in that section is too strong, I'll add the commit case and the WAL vs rollback split. Thanks for actually reproducing it instead of just guessing.