Which PostgreSQL locking to use: a short implementation decision

Question

Prerequisites

• PostgreSQL knowledge: transaction isolation levels and explicit locking.
• Team decisions: whether your codebase can implement client-side retry loops and keep lock order stable.

Implementation decision you’ll make

• Classify the conflict boundary before you pick a tool. If the decision depends on multiple rows, choose SERIALIZABLE and design a client retry loop that retries on SQLSTATE 40001. If a single row owns the decision, use SELECT ... FOR UPDATE so writers wait at the row. If the protected resource is not a row (tenant, shard, or workflow), use a transaction-scoped advisory lock and agree a shared key convention.

Expected result

• The chosen mechanism will make the failure mode explicit and operationally predictable: serializable transactions fail and require retry logic; row locks block and reveal lock contention; advisory locks rely on shared conventions but avoid row semantics when the resource isn’t row-shaped.

One limitation

• Advisory locks shift correctness from the database to your team's conventions. If any participant skips the lock or uses a different key, protection collapses silently. Use advisory locks only when all code paths and teams can be made to follow the same keying and acquisition protocol.

The server will insert the verified implementation example below. Use it as a decision aid, not a substitute for integration tests against a live PostgreSQL instance.

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