In which cases shall sqlite3_prepare return SQLITE_BUSY?
(1) By Vitalije (vitalije) on 2021-01-10 14:48:35 [link] [source]
Hello,
I am developing a http server in rust using actix-web and
r2d2-rusqlite. If I understand well r2d2-rusqlite keeps a
pool of sqlite3 connections. Actix-web runs several threads
for handling incoming http requests. Each thread acquires
an idle sqlite3 connection from the pool. Once this connection is
taken from the pool, no other thread can get it, until it is returned
to the pool by the thread that acquired it. When this thread terminates,
the connection is returned to the pool.
Now, I've found in logs that the call to the sqlite3_prepare returned (for me unexpected) result SQLITE_BUSY. I'am trying to understand what really has happened.
I am not familiar with sqlite3 internals. Can someone explain me is it really possible that sqlite3_prepare can not finish because database is locked? What can I do to prevent this from happening? Or perhaps I need to check every call to sqlite3_prepare for this condition?
Thank you very much. Vitalije
(2) By Richard Hipp (drh) on 2021-01-10 15:07:41 in reply to 1 [source]
The sqlite3_prepare() interface has to read the database schema upon first use, or after a schema change. Perhaps some other connection is modifying the database at the same moment.
Suggestions:
Set WAL mode using PRAGMA journal_mode=WAL. You only have to do this once per database, as the setting is sticky. This will probably be sufficient to solve your problem.
Set a busy timeout using PRAGMA busy_timeout. This will cause SQLite to wait and retry (if it can) when it encounters a lock, rather than giving up immediately.
(3) By Vitalije (vitalije) on 2021-01-10 15:59:55 in reply to 2 [link] [source]
Thank you for quick answer. I've already set busy_timeout. Now I'll try to set journal mode and will report later if it solves the problem.