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

Question

In my Rust application I am using Diesel to interact with an SQLite database. I have multiple threads that may query at the same time the database, and I am using the crate r2d2 to create a pool of connections.

The issue that I am seeing is that I am not able to concurrently query the database. If I try to do that, I always get the error database is locked, which is unrecoverable (any following request will fail from the same error even if only a single thread is querying).

The following code reproduces the issue.

# Cargo.toml
[dependencies]
crossbeam = { version = "0.7.1" }
diesel = { version = "1.4.2", features = ["sqlite", "r2d2"] }

-- The database table
CREATE TABLE users (
    name TEXT PRIMARY KEY NOT NULL
);

#[macro_use]
extern crate diesel;

mod schema;

use crate::schema::*;
use crossbeam;
use diesel::r2d2::{ConnectionManager, Pool};
use diesel::RunQueryDsl;
use diesel::{ExpressionMethods, SqliteConnection};

#[derive(Insertable, Queryable, Debug, Clone)]
#[table_name = "users"]
struct User {
    name: String,
}

fn main() {
    let db_url = "test.sqlite3";
    let pool = Pool::builder()
        .build(ConnectionManager::<SqliteConnection>::new(db_url))
        .unwrap();

    crossbeam::scope(|scope| {
        let pool2 = pool.clone();
        scope.spawn(move |_| {
            let conn = pool2.get().unwrap();
            for i in 0..100 {
                let name = format!("John{}", i);
                diesel::delete(users::table)
                    .filter(users::name.eq(&name))
                    .execute(&conn)
                    .unwrap();
            }
        });

        let conn = pool.get().unwrap();
        for i in 0..100 {
            let name = format!("John{}", i);
            diesel::insert_into(users::table)
                .values(User { name })
                .execute(&conn)
                .unwrap();
        }
    })
    .unwrap();
}

This is the error as shown when the application panics:

thread '<unnamed>' panicked at 'called `Result::unwrap()` on an `Err` value: DatabaseError(__Unknown, "database is locked")'

AFAIK, I should be able to use the connection pool with multiple threads (that is, multiple connections for multiple threads), as shown in the r2d2_sqlite crate example.

Moreover, the sqlite3 library I have installed in my system supports the Serialized threading model, which from here:

In serialized mode, SQLite can be safely used by multiple threads with no restriction.

How can I avoid the database is locked errors? Also, if these errors are not avoidable for any reason, how can I unlock the database?

Answer 1

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.

Answer 2

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();