{"id":1213,"hash":"d2d171f8c02fc8d3626c06554990a5b80c9eee421d279f87f0739326d5ef88d6","pattern":"How to use Diesel with SQLite connections and avoid `database is locked` type of errors","full_message":"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.\n\nThe 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).\n\nThe following code reproduces the issue.\n\n# Cargo.toml\n[dependencies]\ncrossbeam = { version = \"0.7.1\" }\ndiesel = { version = \"1.4.2\", features = [\"sqlite\", \"r2d2\"] }\n\n-- The database table\nCREATE TABLE users (\n    name TEXT PRIMARY KEY NOT NULL\n);\n\n#[macro_use]\nextern crate diesel;\n\nmod schema;\n\nuse crate::schema::*;\nuse crossbeam;\nuse diesel::r2d2::{ConnectionManager, Pool};\nuse diesel::RunQueryDsl;\nuse diesel::{ExpressionMethods, SqliteConnection};\n\n#[derive(Insertable, Queryable, Debug, Clone)]\n#[table_name = \"users\"]\nstruct User {\n    name: String,\n}\n\nfn main() {\n    let db_url = \"test.sqlite3\";\n    let pool = Pool::builder()\n        .build(ConnectionManager::<SqliteConnection>::new(db_url))\n        .unwrap();\n\n    crossbeam::scope(|scope| {\n        let pool2 = pool.clone();\n        scope.spawn(move |_| {\n            let conn = pool2.get().unwrap();\n            for i in 0..100 {\n                let name = format!(\"John{}\", i);\n                diesel::delete(users::table)\n                    .filter(users::name.eq(&name))\n                    .execute(&conn)\n                    .unwrap();\n            }\n        });\n\n        let conn = pool.get().unwrap();\n        for i in 0..100 {\n            let name = format!(\"John{}\", i);\n            diesel::insert_into(users::table)\n                .values(User { name })\n                .execute(&conn)\n                .unwrap();\n        }\n    })\n    .unwrap();\n}\n\nThis is the error as shown when the application panics:\n\nthread '<unnamed>' panicked at 'called `Result::unwrap()` on an `Err` value: DatabaseError(__Unknown, \"database is locked\")'\n\nAFAIK, 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.\n\nMoreover, the sqlite3 library I have installed in my system supports the Serialized threading model, which from here:\n\n  In serialized mode, SQLite can be safely used by multiple threads with\n  no restriction.\n\nHow can I avoid the database is locked errors? Also, if these errors are not avoidable for any reason, how can I unlock the database?","ecosystem":"cargo","package_name":"database","package_version":null,"solution":"Recently I also stumbled onto this problem. Here's what I found.\n\nSQLite does not support multiple writers.\n\nFrom documentation:\n\nWhen SQLite tries to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY.\n\nSo how to get around this limitation ? There are two solutions I see.\n\nBusy timeout\nYou can retry the query multiple times until lock has been acquired.\nIn fact SQLite provides built-in mechanism.\nYou can instruct the SQLite to try lock the database multiple times.\n\nNow the only thing you need is to somehow pass this pragma to SQLite.\nFortunately diesel::r2d2 gives an easy way to pass initial setup for a newly established connection:\n\n#[derive(Debug)]\npub struct ConnectionOptions {\n    pub enable_wal: bool,\n    pub enable_foreign_keys: bool,\n    pub busy_timeout: Option<Duration>,\n}\n\nimpl diesel::r2d2::CustomizeConnection<SqliteConnection, diesel::r2d2::Error>\n    for ConnectionOptions\n{\n    fn on_acquire(&self, conn: &mut SqliteConnection) -> Result<(), diesel::r2d2::Error> {\n        (|| {\n            if self.enable_wal {\n                conn.batch_execute(\"PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL;\")?;\n            }\n            if self.enable_foreign_keys {\n                conn.batch_execute(\"PRAGMA foreign_keys = ON;\")?;\n            }\n            if let Some(d) = self.busy_timeout {\n                conn.batch_execute(&format!(\"PRAGMA busy_timeout = {};\", d.as_millis()))?;\n            }\n            Ok(())\n        })()\n        .map_err(diesel::r2d2::Error::QueryError)\n    }\n}\n\n// ------------- Example -----------------\n\n    let pool = Pool::builder()\n        .max_size(16)\n        .connection_customizer(Box::new(ConnectionOptions {\n            enable_wal: true,\n            enable_foreign_keys: true,\n            busy_timeout: Some(Duration::from_secs(30)),\n        }))\n        .build(ConnectionManager::<SqliteConnection>::new(db_url))\n        .unwrap();\n\nWAL mode\nThe 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).\nNote however that busy timeout is still required for all of this to work.\n\n(Please, read also about consequences of \"synchronous\" mode set to \"NORMAL\".)\n\nSQLITE_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.\n\nThis 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.","confidence":0.95,"source":"stackoverflow","source_url":"https://stackoverflow.com/questions/57123453/how-to-use-diesel-with-sqlite-connections-and-avoid-database-is-locked-type-of","votes":14,"created_at":"2026-04-19T04:52:37.213426+00:00","updated_at":"2026-04-19T04:52:37.213426+00:00"}