In which cases shall sqlite3_prepare return SQLITE_BUSY?

(1) By Vitalije (vitalije) on 2021-01-10 14:48:35 [link]

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

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](https://www.sqlite.org/wal.html) using 
     [PRAGMA journal_mode=WAL](https://www.sqlite.org/pragma.html#pragma_journal_mode).
     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](https://www.sqlite.org/pragma.html#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]

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.