could not serialize access

This error occurs in SERIALIZABLE isolation level when concurrent transactions conflict. Learn about transaction isolation and retry strategies.

The could not serialize access error occurs when concurrent transactions conflict.

Understanding the Error

ERROR: could not serialize access due to concurrent update

In SERIALIZABLE isolation, PostgreSQL detected a conflict that would violate serializable execution.

Common Causes

1. Concurrent Updates to Same Row

SQL

-- Transaction A
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Transaction B (concurrent)
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;  -- Conflict!

2. Write Skew Anomaly

SQL

-- Two transactions reading and writing based on same condition
-- Both might make decisions based on stale data

3. High Concurrency on Same Data

Many concurrent transactions touching the same rows.

How to Fix It

Solution 1: Retry the Transaction

JAVASCRIPT

async function withRetry(fn, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await fn();
    } catch (error) {
      if (error.code === '40001' && attempt < maxRetries) {
        // Wait before retry with exponential backoff
        await sleep(Math.pow(2, attempt) * 100);
        continue;
      }
      throw error;
    }
  }
}

await withRetry(async () => {
  await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
  await client.query('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
  await client.query('COMMIT');
});

Solution 2: Use Lower Isolation Level

SQL

-- REPEATABLE READ has fewer conflicts
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- READ COMMITTED is the default and most lenient
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Solution 3: Use SELECT FOR UPDATE

SQL

-- Lock the row first to prevent conflicts
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Solution 4: Reduce Transaction Scope

SQL

-- Instead of one long transaction
BEGIN;
-- ... lots of reads ...
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Do reads outside transaction, only lock for update
SELECT * FROM accounts WHERE id = 1;  -- Read first
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Solution 5: Use Advisory Locks

SQL

-- Application-level locking
SELECT pg_advisory_lock(hashtext('account_1'));
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SELECT pg_advisory_unlock(hashtext('account_1'));

Transaction Isolation Levels

SQL

-- Most restrictive, prevents all anomalies
SERIALIZABLE

-- Prevents dirty reads and non-repeatable reads
REPEATABLE READ

-- Prevents dirty reads only (PostgreSQL default)
READ COMMITTED

Best Practices

Implement retry logic for serialization failures
Use appropriate isolation level for your needs
Keep transactions short to reduce conflicts
Consider optimistic locking patterns
Monitor serialization failures in production