SQLite "database is locked" reproducer

multiple_writes.rs

//! A reproducer of the 'database is locked' error.
//! https://www2.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked
//!
//! The error happens like this:
//! ```
//! thread 'tokio-runtime-worker' panicked at 'called `Result::unwrap()` on an `Err` value: Database(SqliteError { code: 5, message: "database is locked" })', src/bin/multiple.rs:35:22
//! ```
//! The code is "5", while the wiki page says it is 6.
//!
//! ```toml
//! [dependencies]
//! anyhow = "1.0.65"
//! futures = "0.3.24"
//! nanoid = "0.4.0"
//! sqlx = { version = "0.6.1", features = ["runtime-tokio-rustls", "sqlite"] }
//! tokio = { version = "1.21.1", features = ["full"] }
//! ```

use anyhow::Result;
use nanoid::nanoid;
use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
use std::str::FromStr;
use std::time::Duration;
use tokio::task::JoinHandle;

#[tokio::main]
async fn main() -> Result<()> {
    let connection_pool = SqlitePoolOptions::new()
        .connect_with(
            SqliteConnectOptions::from_str("sqlite://test.db")
                .unwrap()
                .create_if_missing(true)
                .serialized(false),
        )
        .await?;

    sqlx::query("drop table if exists t")
        .execute(&connection_pool)
        .await?;
    sqlx::query("create table t(x text)")
        .execute(&connection_pool)
        .await?;

    let tasks: Vec<JoinHandle<()>> = (0..1000)
        .map(|_| {
            let connection_pool = connection_pool.clone();
            tokio::spawn(async move {
                let mut tx = connection_pool.begin().await.unwrap();
                sqlx::query("insert into t values (?)")
                    .bind(nanoid!())
                    .execute(&mut tx)
                    .await
                    .unwrap();
                tokio::time::sleep(Duration::from_millis(100)).await;
                tx.commit().await.unwrap();
            })
        })
        .collect();
    drop(connection_pool);

    futures::future::join_all(tasks).await;

    Ok(())
}

read_and_write.rs

//! The error happens like this:
//! ```
//! task1: start
//! task2: start
//! task1: committing
//! thread 'tokio-runtime-worker' panicked at 'called `Result::unwrap()` on an `Err` value: Database(SqliteError { code: 5, message: "database is locked" })', src/main.rs:44:27
//! ```
//!
//! ```toml
//! [dependencies]
//! anyhow = "1.0.65"
//! futures = "0.3.24"
//! sqlx = { version = "0.6.1", features = ["runtime-tokio-rustls", "sqlite"] }
//! tokio = { version = "1.21.1", features = ["full"] }
//! ```

use anyhow::Result;
use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
use sqlx::Acquire;
use std::str::FromStr;
use std::time::Duration;

#[tokio::main]
async fn main() -> Result<()> {
    let connection_pool = SqlitePoolOptions::new()
        .connect_with(
            SqliteConnectOptions::from_str("sqlite://test.db")
                .unwrap()
                .create_if_missing(true)
                .serialized(false),
        )
        .await?;

    sqlx::query("drop table if exists t")
        .execute(&connection_pool)
        .await?;
    sqlx::query("create table t(x integer)")
        .execute(&connection_pool)
        .await?;
    sqlx::query("insert into t values(1)")
        .execute(&connection_pool)
        .await
        .unwrap();
    sqlx::query("insert into t values(2)")
        .execute(&connection_pool)
        .await
        .unwrap();

    let mut conn1 = connection_pool.acquire().await?;
    let mut conn2 = connection_pool.acquire().await?;

    let task1 = tokio::spawn(async move {
        println!("task1: start");
        let mut tx = conn1.begin().await.unwrap();
        sqlx::query("insert into t values(3)")
            .execute(&mut tx)
            .await
            .unwrap();
        println!("task1: committing");
        tx.commit().await.unwrap();
        println!("task1: end");
    });

    let task2 = tokio::spawn(async move {
        println!("task2: start");
        let mut tx = conn2.begin().await.unwrap();
        sqlx::query("select * from t")
            .fetch_all(&mut tx)
            .await
            .unwrap();
        tokio::time::sleep(Duration::from_secs(30)).await;
        println!("task2: committing");
        tx.commit().await.unwrap();
        println!("task2: end");
    });

    tokio::try_join!(task1, task2)?;
    Ok(())
}