Concurrency & locking

When two transactions touch the same rows at once, one of them has to yield. LiteORM gives you both ways to arbitrate that: pessimistic row locks that make a reader wait (or skip, or fail fast), and an optimistic version-column pattern that lets writers race and detects the loser. Whichever you pick, contention that the database reports as retryable is handled by liteorm.TransactionRetry, and everything here builds on transactions and the normalized errors.

A locking read takes a lock on the rows it selects, held until the surrounding transaction commits or rolls back — so no one else can change them out from under you. In the query builder these are methods on the select, and they only mean something inside a transaction.

err := liteorm.Transaction(ctx, db, func(tx *liteorm.BoundTx) error {
  // Lock the account row for the duration of the transaction.
  acct, err := query.Select[Account](tx).
    Filter(query.Col[int64]("id").Eq(id)).
    ForUpdate().
    First(ctx)
  if err != nil {
    return err
  }
  // No other transaction can modify this row until we commit.
  if _, err := query.Update[Account](tx).
    Set("balance", acct.Balance-amount).
    Filter(query.Col[int64]("id").Eq(id)).
    Exec(ctx); err != nil {
    return err
  }
  return nil
})

SkipLocked and NoWait both imply a lock, defaulting to FOR UPDATE when you didn’t call ForUpdate/ForShare first. SkipLocked makes the read quietly omit rows another transaction already holds — ideal for handing out work without contention. NoWait makes the read fail fast instead of blocking, so a caller can back off rather than queue.

Row locking is a Postgres and MySQL feature (FeatRowLocking). On SQLite and SQL Server these methods raise a clear build error rather than silently doing nothing — see the single-writer note for why SQLite has no need of them. A lock also can’t be attached to a single arm of a UNION/INTERSECT/EXCEPT compound; apply it to the whole compound instead.

SkipLocked is the canonical way to build a job queue on top of a table: many workers poll the same table, each grabs a batch of unlocked rows, and no two workers ever collide on the same job. Each worker runs one transaction per pull.

type Job struct {
  ID     int64
  Status string `orm:"status"`
  Body   string
}

func pullBatch(ctx context.Context, db *liteorm.DB, n int) error {
  return liteorm.Transaction(ctx, db, func(tx *liteorm.BoundTx) error {
    // Claim up to n pending jobs, skipping any a sibling worker already holds.
    jobs, err := query.Select[Job](tx).
      Filter(query.Col[string]("status").Eq("pending")).
      OrderBy("id").
      Limit(n).
      SkipLocked(). // implies FOR UPDATE
      All(ctx)
    if err != nil {
      return err
    }
    for _, j := range jobs {
      if err := process(ctx, j); err != nil {
        return err // whole batch rolls back; jobs stay pending
      }
      if _, err := query.Update[Job](tx).
        Set("status", "done").
        Filter(query.Col[int64]("id").Eq(j.ID)).
        Exec(ctx); err != nil {
        return err
      }
    }
    return nil
  })
}

Because the claimed rows stay locked until commit, a second worker running the same query at the same instant sees straight past them and claims the next unlocked batch — no double-processing, no explicit “claimed” flag, no lease table.

Pessimistic locks are the wrong tool when contention is rare and holding a lock across think-time (a user editing a form, an HTTP round trip) would serialize everyone. The optimistic pattern instead lets writers proceed unblocked and detects a conflict at write time: carry an integer version column, and make every update assert the version it read and bump it in the same statement.

type Document struct {
  ID      int64
  Body    string
  Version int64 `orm:"version"`
}

Read the row (no lock), do your work, then write conditionally on the version you saw. The multi-row query.Update builder returns the rows-affected count, which is your conflict signal:

doc, err := query.Select[Document](db).
  Filter(query.Col[int64]("id").Eq(id)).
  First(ctx)
if err != nil {
  return err
}

doc.Body = edited

n, err := query.Update[Document](db).
  Set("body", doc.Body).
  Inc("version", 1). // version = version + 1, atomically
  Filter(
    query.Col[int64]("id").Eq(doc.ID),
    query.Col[int64]("version").Eq(doc.Version), // only if nobody else wrote
  ).
  Exec(ctx)
if err != nil {
  return err
}
if n == 0 {
  return errStaleWrite // someone bumped the version first — reload and retry
}

When two clients read version = 7 and both try to write, the database applies exactly one — that update matches and moves the row to version = 8; the other now matches zero rows (n == 0) and knows its copy is stale. No locks are held between the read and the write, so slow clients never block fast ones. The trade-off is that the losing client must reload and reapply its change, so this shines when conflicts are infrequent.

Under serializable or repeatable read isolation the database may abort a transaction it can’t order safely — a serialization failure — or break a deadlock by killing one participant. Both are transient: the correct response is to run the whole unit of work again. LiteORM normalizes them to liteorm.ErrSerialization and liteorm.ErrDeadlock, and liteorm.IsRetryable is true for either, identically across every backend.

liteorm.TransactionRetry is the retry loop written once. It runs your function in a fresh transaction, and while the error is retryable it retries up to RetryPolicy.Max attempts, waiting per the optional Backoff; any non-retryable error (or success) returns immediately.

err := liteorm.TransactionRetry(ctx, db, liteorm.RetryPolicy{
  Max:     5,
  Backoff: func(attempt int) time.Duration { return time.Duration(attempt) * 10 * time.Millisecond },
}, func(tx *liteorm.BoundTx) error {
  return transfer(ctx, tx, from, to, amount)
})

Each attempt begins a new transaction, so the function must be safe to run more than once — read your inputs and compute inside the closure rather than closing over state mutated by a prior attempt. A nil Backoff retries immediately; a cancelled ctx stops the wait between attempts and returns the context error.

You can pair this with the optimistic pattern above: return ErrSerialization (or a retryable sentinel) on a stale write and let TransactionRetry reload-and-reapply for you, turning a hand-rolled retry loop into one call.

To run at an isolation level higher than the backend default, begin the transaction yourself with db.BeginTx(ctx, liteorm.TxOptions{IsoLevel: "serializable"}); the retryable-error normalization and IsRetryable classification work the same whether you use the closure helpers or a manual BeginTx.

SQLite doesn’t offer FOR UPDATE because it doesn’t need it: a SQLite database has at most one writer at a time. A write transaction takes a database-wide write lock, so row-level locking would be redundant — the whole file is already serialized. This is why the locking methods raise a build error on the SQLite dialect rather than pretending to lock.

The practical consequences: enable WAL mode so readers don’t block the writer (and vice versa), and set a busy_timeout so a writer that finds the lock held waits briefly instead of failing instantly with SQLITE_BUSY. Even so, a contended SQLite write can surface a lock-timeout error, so wrapping write paths in TransactionRetry is a reasonable belt-and-suspenders even on SQLite. When you outgrow a single writer, the same code — locking reads included — runs against Postgres or MySQL unchanged.

• Transactions — Transaction, TransactionRetry, savepoints, and running both front-ends on one tx.
• Errors — ErrSerialization, ErrDeadlock, and IsRetryable.
• Writing data — the Update/Delete builders and Inc/RETURNING.
• Full API: liteorm.org and liteorm.org/query.