Pessimistic row locking

Optimistic locking detects a conflict after it happens. Row locking prevents another transaction from acquiring the same lock while the first workflow decides what to do.

Edit and audit together

An editor must capture the body it actually replaces. Read that body under a lock, then write the replacement and history entry through the same transaction client. The PostgreSQL typed-document example adds an audit model:

model ArticleAudit {
  id BigInt @id
  article_id BigInt
  before ArticleBody
  after ArticleBody
  article Article @relation(fields: [article_id], references: [id])
}

Article also declares audits ArticleAudit[]. Both snapshots use the same generated ArticleBody value type as the current document:

use anyhow::Context as _;

client
    .transaction_app(async |tx| {
        let article = tx
            .article()
            .id_eq(article_id)
            .for_update()
            .only()
            .await?;

        let before = article
            .typed_body
            .clone()
            .context("article has no typed body")?;

        tx.update_article_one(&article)
            .typed_body(replacement.clone())
            .save_one()
            .await?;

        tx.create_article_audit()
            .id(audit_id)
            .article_id(article.id)
            .before(before)
            .after(replacement)
            .save()
            .await?;

        Ok::<(), anyhow::Error>(())
    })
    .await?;

Another writer following the same locking protocol waits until this transaction ends, then reads the committed replacement as its own previous body. Keep checks and timestamps that depend on that state after acquiring the lock. Use no_wait() to return EntityError::LockUnavailable immediately instead of waiting.

The generated text length and full-text vector change with the body in the same database transaction. If saving the audit fails, propagating the error with ? rolls back the edit too. Catching the audit error and returning Ok would commit the edit without its history, so that is not appropriate for this invariant.

let article = client
    .article()
    .id_eq(article_id)
    .with_audits_count(|audits| audits)
    .only()
    .await?;

let edit_count = article.audits_count()?;

Generated for_update() queries require a transaction. For queries without permission enforcement, PostgreSQL, MySQL, and MariaDB take row locks. Permission-controlled locking reads currently support PostgreSQL. See the scoped example below. SQLite serializes writers instead and the generated method takes no row lock there. Use atomic updates for a simple increment and optimistic locking when a version check fits the workflow.

Build a SQL lock clause

The lower-level SQL builder exposes the lock's strength, target tables, and wait behavior explicitly:

let locked = Select::new(
    Dialect::Postgres,
    TableRef::new("posts"),
    ["id", "version"],
)
    .lock(
        LockClause::new(LockStrength::Update)
            .tables(["posts"])
            .wait(LockWait::NoWait),
    );

locked.validate()?;

Lock an authorized resource

A scoped PostgreSQL client uses the same query shape:

scoped.transaction(async |tx| {
    let post = tx.post().id_eq(post_id).for_update().only().await?;
    tx.update_post_one(&post).title(replacement.clone()).save_one().await
}).await?;

The read rule filters the locking query. The update still needs its own authority. The lock lasts until this application transaction commits or rolls back. Keep whole-transaction retries for serialization failures. See permission-controlled row locks for backend, isolation and query-shape limits. This capability does not require any new RSL declaration.

Complete example

Download this project, or copy the files below into an empty directory.

Replace RENT_REPOSITORY_URL in Cargo.toml with the URL supplied with your preview access. Use the matching Rent CLI release.

rent generate
cargo run

The project includes its database setup. Each lesson uses a small, self-contained schema. You can run it without applying the earlier lessons.

[package]
name = "rent-lesson-08-row-locking"
version = "0.1.0"
edition = "2024"

[workspace]

[[bin]]
name = "rent-lesson-08-row-locking"
path = "main.rs"

[dependencies]
rent = { git = "RENT_REPOSITORY_URL", tag = "v0.8.6", default-features = false, features = ["sqlite", "migrate", "extensions"] }
anyhow = "1.0.104"
rent-schema-language = { git = "RENT_REPOSITORY_URL", tag = "v0.8.6" }

schema = "schema.rsl"
generated = "generated"
migrations = "migrations"

[database]
dialect = "sqlite"
backends = ["sqlite"]

datasource db {
  provider = "postgresql"
}

model Post {
  id      BigInt  @id
  version UInt

  @@map("posts")
}

use anyhow::{Result, ensure};
use rent::sql::Dialect;
use rent::sql::ast::{LockClause, LockStrength, LockWait, Select, SelectError, TableRef};

fn exercise() -> Result<()> {
    rent_schema_language::parse_schema("schema.rsl", include_str!("schema.rsl"))?;
    let locked = Select::new(Dialect::Postgres, TableRef::new("posts"), ["id", "version"])
        .lock(LockClause::new(LockStrength::Update).tables(["posts"]).wait(LockWait::NoWait));
    locked.validate()?;
    ensure!(locked.render().sql == "SELECT \"id\", \"version\" FROM \"posts\" FOR UPDATE OF \"posts\" NOWAIT");

    let unsupported =
        Select::new(Dialect::Sqlite, TableRef::new("posts"), ["id"]).lock(LockClause::new(LockStrength::Update));
    ensure!(matches!(
        unsupported.validate(),
        Err(SelectError::UnsupportedLock(Dialect::Sqlite))
    ));
    Ok(())
}

fn main() -> Result<()> {
    exercise()
}

#[cfg(test)]
mod tests {
    #[test]
    fn example_runs() -> anyhow::Result<()> {
        super::exercise()
    }
}