Locks inside the database

• Readers never wait for a row lock. They read their snapshot.
• Writers and locking reads on the same row wait until the holder commits or rolls back.
• Locks on different rows never wait for each other.

A row lock lives in the row itself. Other writers queue on the holder's transaction, so one hot row commits one writer at a time.

An application mutex covers one process. A Redis lock adds a second store that can lose the lock in a failover. The row lock already sits next to the data.

I lock the narrowest thing: a row before a table, and a row before a lock service.

FOR UPDATE conflicts with everything. FOR KEY SHARE conflicts only with FOR UPDATE. A plain UPDATE takes FOR NO KEY UPDATE.

• Each statement's conflicts in the matrix match the mode in this table.
• A key column has a unique index that a foreign key can use.
• FOR SHARE: many readers hold the row, and no one may change it.

Postgres takes row locks for me on every write. I add a locking read only when I decide from a value before I write it.

• Use CREATE INDEX CONCURRENTLY on a live table. Plain CREATE INDEX takes SHARE, which blocks every write until it ends.
• A waiting ACCESS EXCLUSIVE request blocks new reads too. We tested it: a SELECT that arrives after a waiting ALTER waits as well.

Every statement takes a table lock. DML takes weak ones that rarely conflict. Most ALTER TABLE forms take ACCESS EXCLUSIVE, which conflicts with every query.

Postgres gives me row locks with no count limit and four modes. It adds SKIP LOCKED for queues, NOWAIT and timeouts to fail fast, advisory locks, and a deadlock detector.

claim():                                  // each worker, in a loop
  UPDATE jobs SET state = running3
  WHERE id = (
    first ready job by id1
    that no other worker has locked           // SKIP LOCKED2
  )
  RETURN its id, or nothing

worker():
  job = claim()
  IF nothing: sleep, then try again4
  do the work; mark the job done

UPDATE jobs SET state = 'running'
WHERE id = (
  SELECT id FROM jobs
  WHERE state = 'ready'
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id;

// Claim takes the first ready job that no other worker holds, marks it running and returns its
// id. Workers never wait for each other: a locked job is skipped. ok is false when no job is
// free.
func Claim(ctx context.Context, db *pgxpool.Pool) (id int, ok bool, err error) {
  err = db.QueryRow(ctx, schema["claim"]).Scan(&id)
  if errors.Is(err, pgx.ErrNoRows) {
    return 0, false, nil
  }
  if err != nil {
    return 0, false, fmt.Errorf("claim a job: %w", err)
  }
  return id, true, nil
}

• Tested: 16 workers claimed 500 jobs, and each job went to exactly one worker.
• A worker that crashes rolls back, so its job is ready again.

Workers claim jobs with FOR UPDATE SKIP LOCKED. Each one gets a different job, and no worker waits for another.

NOWAIT and lock_timeout fail with 55P03. SKIP LOCKED never waits. A deadlock costs one second and one cancelled transaction.

ERROR:  deadlock detected
DETAIL: Session 1 waits for ShareLock on the transaction of session 2.
Session 2 waits for ShareLock on the transaction of session 1.
HINT:   See server log for query details.

• In this run Postgres cancelled session 1. The documentation says the choice is hard to predict, so retry either one.
• The other session's statement completes as soon as the cancelled one rolls back.

A deadlock is a wait cycle. Postgres cancels one transaction after deadlock_timeout. I prevent it by locking rows in one order, and I retry 40P01.

• Set lock_timeout with SET LOCAL inside the transaction, so it covers only that work.
• Run DDL with a short lock_timeout and retry it. A waiting ALTER then never blocks the reads that queue behind it.

Postgres waits for a lock forever by default. I set lock_timeout for DDL and for user requests, and statement_timeout as a ceiling.

with_lock(name, work):
  BEGIN
    take advisory lock hash(name)1       // waits for the holder2
    work()                              // one caller at a time
  COMMIT                                // the lock ends here3

// WithAdvisoryLock runs fn in a transaction that holds the advisory lock for name. Callers with
// the same name run one at a time; the lock ends with the transaction, even on a crash.
func WithAdvisoryLock(ctx context.Context, db *pgxpool.Pool, name string, fn func(pgx.Tx) error) error {
  return pgx.BeginFunc(ctx, db, func(tx pgx.Tx) error {
    // hashtext maps the name to the integer key that advisory locks take.
    if _, err := tx.Exec(ctx, `SELECT pg_advisory_xact_lock(hashtext($1))`, name); err != nil {
      return fmt.Errorf("advisory lock %q: %w", name, err)
    }
    return fn(tx)
  })
}

• Tested: 16 callers read, paused and wrote one value under one name. No increment was lost.
• Session-level advisory locks outlive the transaction. With a connection pool, prefer the transaction-level form.

For work that is not one row, such as a monthly payout run, I take a transaction-level advisory lock on a hash of its name.

• This panel is about MySQL InnoDB. Its default level is REPEATABLE READ.
• A next-key lock is an index-record lock plus a lock on the gap before the record.
• Gap locks only stop inserts into the gap. Two gap locks on one gap do not conflict.
• At READ COMMITTED, InnoDB uses gap locks only for foreign key and duplicate key checks.

Source: the MySQL 8.0 reference manual, InnoDB locking. Not measured in our lab.

MySQL InnoDB locks gaps between index records at REPEATABLE READ. Postgres has no gap locks; it uses snapshots and SIREAD locks.

A lock-and-update transaction is FOR UPDATE on one row, then an UPDATE, then COMMIT. A job claim is the SKIP LOCKED statement in panel G.

Row locks on spread rows run many thousands of transactions a second. I act only when one row gets hot: split it, queue with SKIP LOCKED, or give the key one writer.