0

Error message:

thread 'actix-rt|system:0|arbiter:1' panicked at 'Error deleting task: DatabaseError(__Unknown, "database is locked")', src\handlers\tasks_handlers.rs:54:10
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

Rust code:

#[delete("/lists/{list_id}")]
async fn delete_list_and_tasks(
    pool: web::Data<SqlitePool>,
    list_id: web::Path<i32>,
) -> HttpResponse {
    use crate::db::schema::lists::dsl::{id as list_id_field, lists};
    use crate::db::schema::tasks::dsl::{list_id as tasks_list_id_field, tasks};

    let id = list_id.into_inner();
    let conn = pool.get().expect("Failed to get DB connection from pool");

    let deleted_tasks: Vec<Tasks> = tasks.filter(tasks_list_id_field.eq(id))
        .load(&conn)
        .expect("Error loading tasks with the same list_id");

    let deleted_list: Lists = lists.find(id).get_result(&conn)
        .expect("Error loading list");

    conn.transaction::<_, diesel::result::Error, _>(|| {
        diesel::delete(tasks.filter(tasks_list_id_field.eq(id)))
            .execute(&conn)
            .expect("Error deleting list");

        diesel::delete(lists.filter(list_id_field.eq(id)))
            .execute(&conn)
            .expect("Error deleting list");

        Ok(())
    }).expect("Error deleting list and associated tasks");

    let response = Response {
        deleted_list,
        deleted_tasks,
    };

    HttpResponse::Ok().json(response)
}

Purpose is to delete a list from table lists and all tasks with the same list_id in table tasks. The problem occurs when I access tasks table and when I edit or access something (when I execute the same request with Postman all seems good).

I tried to implement transactions and pools, and don't want to implement mutex because I need to access the database many times at the same time.

What can I do?

user4157124
  • 2,809
  • 13
  • 27
  • 42
Jesus Jimenez Cordero
  • 1
  • The actix-web and even diesel code is mostly irrelevant since `"database is locked"` is symptomatic simply based on how your SQLite is configured and what queries/transactions are in-play at the time. I believe a `DELETE` can and/or will result in a lock error if a `SELECT` is being run on the same table at the same time (or vice versa). You can either handle mutual-exclusion yourself, add a retry mechanism for this kind of error, or use a different database. – kmdreko Jul 01 '23 at 00:05
  • @kmdreko but how is possible that Postman execute correctly that request with no database blocking? – Jesus Jimenez Cordero Jul 01 '23 at 02:27

0 Answers0