SQLite Forum

Question about the WAL checkpoint
Login

Question about the WAL checkpoint

(1) By anonymous on 2021-10-11 05:48:30 [link]

quote from https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md

"Checkpointers do not block writers, and writers do not block checkpointers. "

If i want to do checkpoint in other thread and write in main thread,  I has to create 2 connection for the same database in single thread mode?



" There are also circumstances in which long-running readers may prevent a checkpointer from checkpointing the entire wal file"

If a statment finished with sqlite3_step, but not closed so I can reuse it late.  In this case that mean a long-running readers block prevent a checkpointer?

(2) By Keith Medcalf (kmedcalf) on 2021-10-11 06:11:00 in reply to 1

> If a statment finished with sqlite3_step, but not closed so I can reuse it late. In this case that mean a long-running readers block prevent a checkpointer?

If sqlite3_reset is called on the statement either explicitly or implicitly then the statement is no longer executing -- it is completed.  If the last call to sqlite3_step returned SQLITE_ROW then the statement is still executing and the checkpoint operation cannot checkpoint changes subsequent to the start of that transaction to the database.

 > If i want to do checkpoint in other thread and write in main thread, I has to create 2 connection for the same database in single thread mode?

You cannot have more than one thread call into the sqlite3 library (simultaneously) when in single-thread mode.

(3) By anonymous on 2021-10-11 06:42:59 in reply to 2 [link]

Sorry, I mean single process mode.

(4) By Keith Medcalf (kmedcalf) on 2021-10-11 10:13:28 in reply to 3 [link]

It would probably be perspicacious to have the checkpoint operations occur on a separate thread with its own connection if you have turned off autocheckpoint in order to prevent checkpoints from running on the other connections/threads at commit time.