Row-level locks

On PostgreSQL, find, findOne, and findAll accept a row-level lock in their third argument (SqlOperationOptions). The package wraps the generated SELECT with Drizzle's .for(...) clause. On MySQL the lock options are no-ops — the option is silently ignored, and no FOR UPDATE is generated.

When to use

Read-modify-write patterns where you need to prevent another transaction from mutating the row between your read and your update:

• Fetching an account balance, then decrementing it.
• Locking a posts row while computing a slug.
• Anything where a stale read would cause a lost-update bug.

If you only need to fetch data and write it later, prefer executeSqlTransaction and call select(...).for('update') directly — locks are scoped to the transaction and released on commit or rollback.

Options

Two fields on SqlOperationOptions — both Postgres-only:

lock: 'none' is the default — pass it explicitly to override a service-level default. lock and forNoKeyUpdate are mutually exclusive; forNoKeyUpdate wins if both are set.

FOR NO KEY UPDATE is like FOR UPDATE but does not block other transactions from inserting FK-target rows — useful when you're locking a parent row but children may legitimately appear (e.g. appending audit-log entries).

Examples

const account = await accounts.find(id, { lock: 'update' });
// SELECT ... FOR UPDATE

const activeRows = await accounts.findAll(
  { status: 'active' },
  { page: 1, limit: 50 },
  { lock: 'share' },
);
// SELECT ... FROM accounts WHERE ... ORDER BY ... LIMIT 50 OFFSET 0 FOR SHARE

const parent = await posts.findOne({ slug }, { forNoKeyUpdate: true });
// SELECT ... FOR NO KEY UPDATE
// — other transactions can still INSERT child rows referencing this parent

Caveats

• MySQL — the options are accepted but ignored. No FOR UPDATE is added. If you need MySQL locking, drop down to a custom method.
• No assertions under contention. The package's unit tests cover the code path (the option reaches Drizzle's .for(...)); contention behavior is the database's contract, not ours. Verify with a concurrent integration test before relying on it.
• Read methods only. create / update / delete / softDelete / restore have their own transaction model — they don't take a lock option. Use executeSqlTransaction and a find(..., { lock }) inside it if you need to lock across reads and writes.

Next

• → Transactions — executeSqlTransaction for multi-statement locks
• → API reference — full SqlOperationOptions signature