The Hidden Deadlock in Concurrent Upserts: Why ON CONFLICT DO UPDATE Isn't Always Safe

A widespread assumption in application development is that PostgreSQL's INSERT ... ON CONFLICT (key) DO UPDATE guarantees atomic, deadlock-free upsert behavior without requiring explicit table-level locks. While it effectively prevents duplicate key violations, concurrent upserts can still encounter SQLSTATE 40P01: deadlock_detected under real production traffic.

Understanding why this happens requires looking at how PostgreSQL implements speculative insertion and tuple locking during an upsert operation.

The Mechanism: Speculative Insertion

When PostgreSQL executes an INSERT ... ON CONFLICT, it cannot know in advance whether the row exists without consulting the unique index. Rather than acquiring a heavy table-level lock, PostgreSQL uses a two-phase protocol known as speculative insertion:

1. Insert Speculative Heap Tuple: The backend inserts a raw row into the table heap, stamped with a speculative token (an internal transaction identifier).
2. Index Pre-Check: The backend attempts to insert the corresponding index key into the unique index.
3. Resolution:
   • If the index insertion succeeds with no conflict, the speculative token is confirmed and the row becomes visible to other transactions.
   • If a conflict is detected against an already-committed row, the speculative heap tuple is discarded, and execution branches to the DO UPDATE path, locking the existing tuple with an exclusive row lock (XMAX).
   • If the conflicting index entry belongs to another in-flight (uncommitted) transaction, the current transaction must block and wait for that transaction to either COMMIT or ROLLBACK.

Scenario 1: The Multi-Row Batch Deadlock

The most common cause of upsert deadlocks is bulk insertion where multiple workers insert the same set of keys in non-deterministic order.

-- Transaction 1 (Worker A)
INSERT INTO user_stats (user_id, metric, score)
VALUES (101, 'clicks', 5), (202, 'clicks', 3)
ON CONFLICT (user_id, metric) DO UPDATE
SET score = user_stats.score + EXCLUDED.score;

-- Transaction 2 (Worker B, running concurrently)
INSERT INTO user_stats (user_id, metric, score)
VALUES (202, 'clicks', 1), (101, 'clicks', 4)
ON CONFLICT (user_id, metric) DO UPDATE
SET score = user_stats.score + EXCLUDED.score;

Here is how the deadlock unfolds:

• Worker A locks the tuple for user_id = 101 and moves to insert 202.
• Worker B concurrently locks the tuple for user_id = 202 and moves to insert 101.
• Worker A waits for Worker B to release 202; Worker B waits for Worker A to release 101.
• PostgreSQL's deadlock detector triggers after deadlock_timeout (typically 1 second) and aborts one of the transactions with error code 40P01.

Scenario 2: Tables with Multiple Unique Constraints

Deadlocks also arise on single-row upserts when a table has more than one unique constraint or index. For instance, consider an accounts table with a primary key id and a unique email constraint (tenant_id, email):

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    tenant_id INT NOT NULL,
    email TEXT NOT NULL,
    status TEXT NOT NULL,
    CONSTRAINT uq_tenant_email UNIQUE (tenant_id, email)
);

If an upsert targets ON CONFLICT (id) DO UPDATE, PostgreSQL locks the primary key index. However, when writing the updated email value, it must also acquire an index lock on uq_tenant_email. If another concurrent query upserts or updates with conflicting parameters across both constraints simultaneously, index locking order inversion produces an unexpected deadlock cycle.

How to Prevent Upsert Deadlocks

1. Enforce Deterministic Ordering on Batches

If you insert multiple rows within a single statement, always sort the records by their unique conflict keys in application code before constructing the SQL query:

// TypeScript example: Sort records deterministically by conflict key
const sortedRecords = records.sort((a, b) => {
  if (a.userId === b.userId) {
    return a.metric.localeCompare(b.metric);
  }
  return a.userId - b.userId;
});

// Construct parameter list from sortedRecords

When all concurrent workers acquire row locks in the exact same order (e.g. ascending by primary key), circular wait conditions become mathematically impossible.

2. Isolate Batch Writes or Fall Back to Single-Row Upserts

For high-concurrency ingestion pipelines (e.g. Kafka consumers processing event streams), batching rows across distinct workers creates frequent key collisions. Splitting hot records into individual single-row upserts or partitioning message streams by entity key (so one worker handles all updates for a given partition key) eliminates contention at the source.

3. Application-Level Transaction Retries

Even with deterministic ordering, distributed services must implement automatic retry logic for transient database deadlocks:

async function executeWithRetry(queryFn, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await queryFn();
    } catch (err) {
      // 40P01 is PostgreSQL's error code for deadlock_detected
      if (err.code === '40P01' && attempt < maxRetries) {
        const jitter = Math.random() * 50;
        await new Promise((res) => setTimeout(res, attempt * 100 + jitter));
        continue;
      }
      throw err;
    }
  }
}

Summary

ON CONFLICT DO UPDATE is an essential tool for atomic upserts, but it does not bypass PostgreSQL's standard tuple and index locking rules. Always sort batch upsert payloads deterministically, be cautious of secondary unique indexes, and pair high-throughput write paths with backoff retry handlers.