How to fix Serialization failure in PostgreSQL

Catch serialization failures and retry the entire transaction from the beginning. This is the recommended approach as the second attempt will see previously-committed changes.

const MAX_RETRIES = 3;
let retries = 0;

while (retries < MAX_RETRIES) {
  try {
    await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
    // Your transaction logic here
    await client.query('COMMIT');
    break;
  } catch (error) {
    if (error.code === '40001') {
      retries++;
      if (retries >= MAX_RETRIES) throw error;
      await client.query('ROLLBACK');
    } else {
      throw error;
    }
  }
}

Sequential scans force relation-level predicate locks that increase serialization conflicts. Create indexes on columns involved in transaction queries:

CREATE INDEX idx_users_account_id ON users(account_id);
CREATE INDEX idx_orders_user_id ON orders(user_id);

This allows index-level locks instead of relation-level locks, reducing false positive conflicts.

If Serializable isolation causes excessive failures, evaluate if Repeatable Read is sufficient for your use case. Repeatable Read prevents phantom reads but is less strict than Serializable:

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- your transactions
COMMIT;

Note: Repeatable Read can still produce serialization failures but typically at lower rates.

If you have long-running read-only transactions, use the DEFERRABLE option to avoid serialization failures:

BEGIN ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE;
-- your read-only queries
COMMIT;

The session will wait for interfering read-write transactions to complete before starting, guaranteeing no serialization failure.

Reduce the window of potential conflicts by minimizing transaction duration:

- Keep transactions as short as possible
- Move non-database logic outside the transaction
- Break large operations into smaller transactions when safe
- Avoid user interactions inside a transaction

Shorter transactions reduce the overlap window with concurrent transactions.

Identify transactions holding locks that may cause conflicts:

SELECT pid, usename, state, query, state_change 
FROM pg_stat_activity 
WHERE state = 'idle in transaction';

Terminate long-running idle transactions to reduce lock contention and serialization conflicts.