Serializable vs SELECT FOR UPDATE vs advisory locks — pick the boundary, not the loudest tool

When two sessions can read the same state and then both write, the root issue is an unclear ownership boundary, not a missing lock. This article helps you decide between three PostgreSQL approaches by focusing on what owns the conflict and how each option fails.

Implementation summary

• The article ships a tiny decision helper that takes a conflict description and returns one of three strategies plus a retry hint. That helper is deliberately small: it encodes the architectural decision rather than attempting to simulate PostgreSQL behavior. A companion routine emits a client-side retry sketch for SERIALIZABLE transactions. The server inserts the verified implementation example below.

Expected behavior

• SERIALIZABLE: Choose this when the invariant is set-shaped or multi-row and the only safe guarantee is equivalence to some serial order. PostgreSQL enforces the serial-order rule and surfaces serialization failures as SQLSTATE 40001; your application must treat those as retryable errors and implement a retry loop on the client.
• SELECT ... FOR UPDATE: Use when a single row owns the conflict. The waiting point is explicit and visible: acquire the row lock when you read and then update. This is often easier to reason about, but it only protects the rows you lock and can introduce blocking and deadlocks if transactions lock rows in inconsistent orders.
• Transaction-scoped advisory locks: Choose when the protected unit is application-defined (tenant shard, workflow, job key) rather than row-shaped. Advisory locks are powerful because you name the resource, but they rely on every participant using the same convention; if some code path skips the lock or uses a different key, the protection silently fails.

Limitations and the practical decision rule

• Failure shapes: SERIALIZABLE aborts and pushes complexity to retry handling; row locks block and can deadlock; advisory locks fail via protocol drift. Pick the mechanism whose failure you can reliably operate.
• Edge cases: a conflict that looks row-shaped might actually be set-shaped; advisory locks can be the wrong abstraction if the logic naturally lives inside one transaction and you want the database to reject unsafe interleavings.
• Operational constraints: keep critical sections short. Long transactions increase overhead and contention for every approach.

Read the article for the decision checklist, the small helper that encodes it, and clear guidance on where retry logic belongs (on the client, not hidden inside a fake in-database wrapper).

Working example

export function chooseConcurrencyControl({ conflictShape, canRetry, ownsRow, applicationScoped }) {
  if (!conflictShape || typeof conflictShape !== 'string') {
    throw new TypeError('conflictShape must be a non-empty string');
  }

  const shape = conflictShape.trim().toLowerCase();
  if (!shape) throw new TypeError('conflictShape must be a non-empty string');

  if (shape === 'serializable' || shape === 'set' || shape === 'multi-row') {
    return {
      strategy: 'SERIALIZABLE',
      reason: 'The invariant depends on a set or multi-row decision, so the database should detect non-serializable outcomes and the application must retry serialization failures.',
      retry: canRetry !== false
    };
  }

  if (shape === 'row' || ownsRow === true) {
    return {
      strategy: 'SELECT ... FOR UPDATE',
      reason: 'One row owns the conflict, so explicit row locking makes the waiting point direct and predictable.',
      retry: false
    };
  }

  if (shape === 'application' || applicationScoped === true) {
    return {
      strategy: 'TX_ADVISORY_LOCK',
      reason: 'The resource is application-defined rather than row-shaped, so a transaction-scoped advisory lock is a better fit.',
      retry: false
    };
  }

  return {
    strategy: 'SERIALIZABLE',
    reason: 'Default to serializable when the conflict boundary is unclear, because the database can enforce the serial-order rule and reject unsafe interleavings.',
    retry: canRetry !== false
  };
}

export function buildSerializableRetryLoop({ statement, maxAttempts = 3 }) {
  if (typeof statement !== 'string' || statement.trim() === '') {
    throw new TypeError('statement must be a non-empty string');
  }
  if (!Number.isInteger(maxAttempts) || maxAttempts < 1) {
    throw new RangeError('maxAttempts must be a positive integer');
  }

  return [
    'for attempt in 1..maxAttempts:',
    '  BEGIN ISOLATION LEVEL SERIALIZABLE;',
    `  ${statement.trim()}`,
    "  COMMIT;",
    "  if commit succeeds: return success",
    "  if SQLSTATE is '40001': retry",
    '  otherwise: rethrow'
  ].join('\n');
}

export function summarizeTradeoffs() {
  return {
    serializable: 'Use for set-shaped invariants that need database-enforced retry semantics.',
    rowLock: 'Use when a single row is the conflict owner and waiting is acceptable.',
    advisory: 'Use when the protected resource is application-scoped and not naturally a row.'
  };
}

Read the full article: https://chriseugenerodriguez.com/blog/serializable-vs-select-for-update-advisory-locks