What do you think about named/advisory locks?

[nappa85]

Hello guys,
On my project I need named locks on mysql, at the moment I've implemented them with a simple function because I know I'll always run on mysql

use sea_orm::{ConnectionTrait, Statement, Value, Values};

use tracing::{error, instrument};

#[instrument]
pub async fn get_lock<'a, C>(conn: &C, key: &str, timeout: u8) -> Result<bool, ()>
where
    C: ConnectionTrait<'a> + std::fmt::Debug,
{
    let mut stmt = Statement::from_string(conn.get_database_backend(), String::from("SELECT GET_LOCK(?, ?) AS res"));
    stmt.values = Some(Values(vec![Value::from(key), Value::from(timeout)]));
    let res = conn.query_one(stmt)
        .await
        .map_err(|e| error!("MySQL get_lock error: {}", e))?
        .ok_or_else(|| error!("MySQL get_lock unknown error"))?;
    res.try_get::<Option<bool>>("", "res")
        .map_err(|e| error!("MySQL get_lock retrieve error: {}", e))?
        .ok_or_else(|| error!("MySQL get_lock retrieve unknown error"))
}

#[instrument]
pub async fn release_lock<'a, C>(conn: &C, key: &str) -> Result<bool, ()>
where
    C: ConnectionTrait<'a> + std::fmt::Debug,
{
    let mut stmt = Statement::from_string(conn.get_database_backend(), String::from("SELECT RELEASE_LOCK(?) AS res"));
    stmt.values = Some(Values(vec![Value::from(key)]));
    let res = conn.query_one(stmt)
        .await
        .map_err(|e| error!("MySQL release_lock error: {}", e))?
        .ok_or_else(|| error!("MySQL release_lock unknown error"))?;
    res.try_get::<Option<bool>>("", "res")
        .map_err(|e| error!("MySQL release_lock retrieve error: {}", e))?
        .ok_or_else(|| error!("MySQL release_lock retrieve unknown error"))
}

Named locks are useful when simply locking on a key at application level: https://mariadb.com/kb/en/get_lock/
PostgreSQL calls it Advisory Locks: https://www.postgresql.org/docs/9.1/explicit-locking.html#ADVISORY-LOCKS
As far as I know, SQLite doesn't have anything similar.

Are you interested in implementing it upstream or SQLite is a show stopper?

[billy1624]

Hey @nappa85, welcome back! Named checkpoint? Sounds cool

[tyt2y3]

Definitely welcomed!