How to Fix PostgreSQL Error: Serialization Failure

A "Serialization failure" error occurs when PostgreSQL cannot execute transactions as if they ran serially, even though they executed concurrently. This happens under Repeatable Read or Serializable isolation levels when concurrent transactions would produce results inconsistent with any serial execution order.

Impact

Serialization failures cause transaction rollbacks, requiring application retry logic. They're more common in high-concurrency scenarios and with Serializable isolation level. While protecting data consistency, they can impact throughput if not handled properly.

Common Causes

Using Serializable isolation level
Concurrent transactions modifying related data
Read-then-write patterns (check-then-act)
Write skew anomalies
Phantom reads in Serializable mode
Long-running serializable transactions
High contention on specific data

Troubleshooting and Resolution Steps

Identify the specific error:

-- Full error message:
-- ERROR: could not serialize access due to read/write dependencies among transactions
-- DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
-- HINT: The transaction might succeed if retried.

Implement automatic retry:

# Python with psycopg2
from psycopg2 import extensions, OperationalError
import time

def execute_serializable_transaction(operation, max_attempts=5):
    for attempt in range(max_attempts):
        conn = None
        try:
            conn = get_connection()
            conn.set_isolation_level(
                extensions.ISOLATION_LEVEL_SERIALIZABLE
            )

            with conn.cursor() as cursor:
                result = operation(cursor)

            conn.commit()
            return result

        except OperationalError as e:
            if conn:
                conn.rollback()

            error_msg = str(e).lower()
            if 'serializ' in error_msg or 'concurrent' in error_msg:
                if attempt < max_attempts - 1:
                    # Exponential backoff with jitter
                    sleep_time = (0.01 * (2 ** attempt))
                    time.sleep(sleep_time + random.uniform(0, 0.01))
                    continue
            raise
        finally:
            if conn:
                conn.close()

    raise Exception(f"Transaction failed after {max_attempts} attempts")

Use lower isolation level if possible:

-- Repeatable Read has fewer conflicts than Serializable
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Your queries
COMMIT;

-- Or use default Read Committed
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Your queries
COMMIT;

Use explicit locking to avoid conflicts:

-- SELECT FOR UPDATE prevents concurrent modifications
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM accounts
WHERE id IN (1, 2)
ORDER BY id  -- Lock in consistent order
FOR UPDATE;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Handle write skew with constraints:

-- Write skew example: maintaining invariant
-- Problem: Two doctors on call, both try to go off-call simultaneously

-- Solution 1: Use constraint
CREATE TABLE on_call (
    doctor_id INTEGER PRIMARY KEY,
    on_call BOOLEAN DEFAULT false
);

ALTER TABLE on_call ADD CONSTRAINT min_one_on_call
CHECK (
    (SELECT COUNT(*) FROM on_call WHERE on_call = true) >= 1
);

-- Solution 2: Use Serializable
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM on_call WHERE on_call = true;
-- If count > 1, safe to set to false
UPDATE on_call SET on_call = false WHERE doctor_id = 1;
COMMIT;

Minimize transaction scope:

-- BAD: Long transaction
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT data FROM source_table;
-- ... complex application processing ...
INSERT INTO dest_table VALUES (...);
COMMIT;

-- GOOD: Process outside transaction
-- Get data
SELECT data FROM source_table;
-- Process in application
-- Short transaction for write
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO dest_table VALUES (...);
COMMIT;

Use appropriate conflict resolution:

# Different strategies based on operation type

def transfer_money(from_id, to_id, amount):
    # Retry on serialization failure
    return execute_with_retry(
        lambda cursor: perform_transfer(cursor, from_id, to_id, amount)
    )

def log_event(event_data):
    # Don't retry - acceptable to lose occasional log entry
    try:
        execute_serializable(lambda cursor: insert_log(cursor, event_data))
    except SerializationError:
        # Log to file instead
        write_to_file_log(event_data)

Monitor serialization failures:

-- Track serialization failures
SELECT
    datname,
    serialization_failures
FROM pg_stat_database
WHERE datname = current_database();

-- Check active serializable transactions
SELECT
    pid,
    usename,
    application_name,
    state,
    query_start,
    query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
AND state != 'idle';

Optimize transaction ordering:

-- Always access resources in consistent order
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Good: Consistent ordering by ID
SELECT * FROM accounts
WHERE id IN (100, 200)
ORDER BY id  -- Always ascending
FOR UPDATE;

-- Process in same order
UPDATE accounts SET balance = balance - 50 WHERE id = 100;
UPDATE accounts SET balance = balance + 50 WHERE id = 200;

COMMIT;

Consider alternative approaches:

-- Use MATERIALIZED VIEW for read-heavy workloads
CREATE MATERIALIZED VIEW account_summary AS
SELECT account_id, SUM(amount) as total
FROM transactions
GROUP BY account_id;

-- Refresh periodically instead of real-time reads
REFRESH MATERIALIZED VIEW CONCURRENTLY account_summary;

-- Or use event sourcing pattern
-- Append-only transactions, compute balance on read

Additional Information

Serializable isolation provides strongest guarantees but highest conflict rate
Retry with exponential backoff and jitter is essential
Short transactions significantly reduce conflicts
Read Committed isolation avoids serialization failures entirely
Consider if you truly need Serializable isolation
Some workloads are inherently conflict-prone
Application-level solutions (queuing, sharding) may be needed for extreme contention

Frequently Asked Questions

Q: Why do I get serialization failures even without concurrent transactions?
A: Conflicts can occur based on predicate locks and read dependencies, not just row-level locks. The database detects potential anomalies even when actual data conflicts don't occur.

Q: How many retries are reasonable?
A: 3-5 attempts with exponential backoff usually suffice. If still failing, there may be a design issue causing excessive contention.

Q: Can I use Serializable for all transactions?
A: Technically yes, but it's often unnecessary and reduces throughput. Use only when you need to prevent all anomalies.

Q: What's the difference between Serialization failure and Could not serialize access?
A: They're essentially the same - both indicate the transaction couldn't maintain serializable execution order. Error message variations depend on specific conflict type.

Q: Does SELECT FOR UPDATE prevent serialization failures?
A: It can reduce them by explicitly locking rows, but Serializable isolation can still detect conflicts based on read dependencies.

Q: Should I log every retry attempt?
A: Log final failures, but retries are normal. Excessive logging can impact performance. Consider metrics/counters instead.

Q: Can I mix isolation levels in the same database?
A: Yes, different transactions can use different isolation levels. Choose based on each transaction's consistency needs.

Q: How does this relate to eventual consistency?
A: Serializable provides strong consistency (all transactions see same order). Eventual consistency is different approach where conflicts are resolved asynchronously.