How to use Diesel with SQLite connections and avoid `database is locked` type of errors

Recently I also stumbled onto this problem. Here's what I found.

SQLite does not support multiple writers.

From documentation:

When SQLite tries to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY.

So how to get around this limitation ? There are two solutions I see.

Busy timeout

You can retry the query multiple times until lock has been acquired.In fact SQLite provides built-in mechanism.You can instruct the SQLite to try lock the database multiple times.

Now the only thing you need is to somehow pass this pragma to SQLite.Fortunately diesel::r2d2 gives an easy way to pass initial setup for a newly established connection:

#[derive(Debug)]pub struct ConnectionOptions {    pub enable_wal: bool,    pub enable_foreign_keys: bool,    pub busy_timeout: Option<Duration>,}impl diesel::r2d2::CustomizeConnection<SqliteConnection, diesel::r2d2::Error>    for ConnectionOptions{    fn on_acquire(&self, conn: &mut SqliteConnection) -> Result<(), diesel::r2d2::Error> {        (|| {            if self.enable_wal {                conn.batch_execute("PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL;")?;            }            if self.enable_foreign_keys {                conn.batch_execute("PRAGMA foreign_keys = ON;")?;            }            if let Some(d) = self.busy_timeout {                conn.batch_execute(&format!("PRAGMA busy_timeout = {};", d.as_millis()))?;            }            Ok(())        })()        .map_err(diesel::r2d2::Error::QueryError)    }}// ------------- Example -----------------    let pool = Pool::builder()        .max_size(16)        .connection_customizer(Box::new(ConnectionOptions {            enable_wal: true,            enable_foreign_keys: true,            busy_timeout: Some(Duration::from_secs(30)),        }))        .build(ConnectionManager::<SqliteConnection>::new(db_url))        .unwrap();

WAL mode

The second variant you might want to use is WAL mode. It improves concurrency by letting readers and writer to work at the same time (WAL mode is waaay faster than default journal mode).Note however that busy timeout is still required for all of this to work.

(Please, read also about consequences of "synchronous" mode set to "NORMAL".)

SQLITE_BUSY_SNAPSHOT is the next thing that may occur with WAL mode. But there is easy remedy to that - use BEGIN IMMEDIATE to start transaction in write mode.

This way you can have multiple readers/writers which makes life easier. Multiple writers use locking mechanism (through busy_timeout), so there is one active writer at the time. You certainly don't want to qualify connections as read and write and do locking manually in your application, e.g. with Mutex.

I find setting r2d2::Pool::builder().max_size(1) solves the issue, but you need to be careful with your connection management and don't ask if you already got one, for example:

fn create(pool: &DbPool, data: User) {    let conn = pool.get().unwrap(); // One connection    if !exist(pool, data) { // Two connection        diesel::insert_into(users::table)            .values(User { name: data.name })            .execute(&conn)            .unwrap();    }}fn exist(pool: &DbPool, data: User) -> bool {    let conn = pool.get().unwrap();    Ok(        select(exists(users::table.filter(col_user_name.eq(data.name))))            .get_result(&conn)            .unwrap(),    )}

I removed all let conn = pool.get().unwrap(); and only try to get it inside the request.

diesel::insert_into(users::table)    .values(User { name })    .execute(&pool.get().unwrap())    .unwrap();