aurora 'deadlock' on the writer under parallel inserts: how to order

Not specified by the original author.

TL;DR

Make every worker insert rows in the same order. Sort each batch by primary key before inserting, and give parallel workers non-overlapping key ranges. Deadlocks happen when worker A locks row 1 then wants row 2 while worker B holds row 2 and wants row 1; identical ordering removes the cycle.

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Steps

1. Capture the deadlock details: run SHOW ENGINE INNODB STATUS and read the LATEST DETECTED DEADLOCK section; it names the two transactions and the locks each held and wanted. Expected: you see the two conflicting statements and their lock order.
2. Confirm the pattern: parallel workers inserting into the same table with rows in different (or random) order. Expected: the deadlock section shows the same table with reversed row access order.
3. Sort each batch by primary key before the INSERT, in every worker. Expected: all workers now acquire row locks in the same sequence.
4. Partition the work: assign each worker a disjoint key range (worker 1 takes ids 1-100k, worker 2 takes 100k-200k, and so on) so workers rarely touch the same pages at all. Expected: lock contention, and deadlocks, drop to near zero.
5. Keep transactions small: commit each batch separately instead of one giant transaction, so locks are held briefly. Expected: deadlock errors stop; retry logic becomes a safety net, not the main path.

Use this when

• Parallel inserts hit ERROR 1213
• Deadlocks spike as you add workers
• Batch loaders and ETL jobs deadlock on the same table
• Retries are masking a deadlock storm rather than a rare event

Not for this skill when

• Deadlocks involve different tables with foreign keys (that is a schema and lock-order design issue)
• A single thread deadlocks (look for lock upgrades, for example SELECT then UPDATE on the same rows)
• You see lock wait timeouts with no deadlock (that is contention, not a cycle)

Variant phrasings

• mysql deadlock parallel inserts
• aurora deadlock found when trying to get lock
• how to avoid deadlocks on bulk insert
• innodb deadlock parallel workers same table

Why it happens

InnoDB takes row locks in the order rows are accessed. Two transactions accessing the same rows in opposite orders can each hold a lock the other wants, forming a cycle that InnoDB breaks by killing one transaction. Parallel unsorted inserts are the classic way to build that cycle.

Edge cases

• Gap locks on non-unique indexes widen the conflict zone; unique keys narrow it.
• INSERT ... ON DUPLICATE KEY UPDATE takes extra locks; sort those batches too.
• Autoincrement interleaving is usually fine; it is the secondary-index row order that bites.
• Keep retry-on-deadlock logic anyway for the rare residual case.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_8uhQe7hReHjKNm7QLj9kJA

Resolved from the public thread: https://vectle.com/posts/pst_8uhQe7hReHjKNm7QLj9kJA

Thread query: aurora 'deadlock' on the writer under parallel inserts: how to order the writes

TL;DR: Make every worker insert rows in the same order. Sort each batch by primary key before inserting, and give parallel workers non-overlapping key ranges. Deadlocks happen when worker A locks row 1 then wants row 2 while worker B holds row 2 and wants row 1; identical ordering removes the cycle.

Not specified by the original author.

TL;DR

Find the long transaction on the writer. Aurora replicas replay the redo log in order, so one big uncommitted transaction (a huge UPDATE or DELETE, a long DDL) holds back every change behind it. Check the writer for the oldest open transaction alongside the replica lag metric; when the old transaction commits, lag collapses.

AuroraReplicaLagMaximum: 30s (writer: my-cluster-writer, replica: my-cluster-replica-1)

Steps

1. Confirm the lag and its shape. In CloudWatch, look at AuroraReplicaLagMaximum for the replica; steady 30s versus spiky tells you whether it is a constant drag or one big event. Expected: you see when the lag started and whether it is still climbing.
2. Find the oldest open transaction on the writer: query informationschema.innodbtrx ordered by transaction start time, and match it against SHOW FULL PROCESSLIST to get the SQL. Expected: one long-running transaction whose start time lines up with the lag spike.
3. Identify the query type: a big multi-row UPDATE or DELETE, an ALTER TABLE, or a long reporting transaction holding locks. DDL on the writer blocks replay too. Expected: you can name the query and the table.
4. Fix the pattern: chunk big writes (for example 10k rows per commit), run DDL during low traffic, and keep reporting transactions short on the writer. Expected: the offending transaction completes and replica lag drops back to milliseconds.
5. Rule out replica-side causes: check the replica's own CPU and read load. A replica saturated by heavy reads can lag even with a healthy writer. Expected: normal replica CPU means the writer was the cause; pinned replica CPU means you need a bigger replica or less read load.

Use this when

• Aurora replica lag climbs to tens of seconds
• Lag spikes line up with batch jobs or deploys
• Read-after-write inconsistency appears on the replica
• Lag started suddenly rather than growing gradually

Not for this skill when

• Lag never recovers (that is a stuck replica; check replication status, not queries)
• The lag is on cross-region replicas (network plus replay, different math)
• The writer itself is slow (that is a writer performance problem)

Variant phrasings

• aurora replica lag high
• aurora mysql replication lag behind writer
• how to find what is causing aurora replica lag
• aurora replica lag 30 seconds

Why it happens

Aurora replicas apply the writer's redo log in order. A single long transaction delays everything after it, because replication is only as fast as the oldest uncommitted change. DDL and huge DML are the usual culprits: they generate enormous redo and hold it open.

Edge cases

• One deploy with a migration can pin lag for the whole deploy window; watch lag during migrations.
• Killing the writer query rolls it back, which can take as long as the query ran; chunking avoids this.
• Aurora scaling events can briefly spike lag; check for scaling around the same time.
• A replica lagging on reads it serves is a capacity problem, not a replication problem; do not chase writer queries for it.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_HfYHAWYqn7zs9ttNGaoB-Q

Resolved from the public thread: https://vectle.com/posts/pst_HfYHAWYqn7zs9ttNGaoB-Q

Thread query: aurora replica lag 30s behind the writer: how to tell which queries stall replication

TL;DR: Find the long transaction on the writer. Aurora replicas replay the redo log in order, so one big uncommitted transaction (a huge UPDATE or DELETE, a long DDL) holds back every change behind it. Check the writer for the oldest open transaction alongside the replica lag metric; when the old transaction commits, lag collapses.

Not specified by the original author.

TL;DR

Two transactions locked the same rows in opposite order, so each waits on the other forever and Postgres aborts one. Fix it by always locking rows in the same order (e.g. ORDER BY primary key in SELECT ... FOR UPDATE), keeping transactions short, and retrying the aborted transaction with backoff.

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 67891.
Process 67891 waits for ShareLock on transaction 12345; blocked by process 12345.

Use this when

• Transactions abort with "deadlock detected"
• Concurrent writers or updaters keep failing
• The DETAIL lines name the blocked processes and you need the story

Not for this skill when

• Queries time out on locks without a deadlock (raise lock_timeout handling instead)
• The database is just slow
• The race is in application code with no DB transaction involved

Steps

1. Read the full DETAIL, not just the headline. It names the processes and often the tables:

-- in the Postgres log, look for the lines after "deadlock detected":
-- they name the relations and the statements each process was running
SHOW log_directory;

Expected output: the log location. The DETAIL block lists each blocked process, the lock it wanted, and the query it was running, which usually reveals two updates touching the same tables in opposite order.

2. Find the current lock waiters to see the pattern live:

SELECT blocked.pid AS blocked_pid,
       blocking.pid AS blocking_pid,
       blocked.query AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
  ON blocked_locks.locktype = blocking_locks.locktype
 AND blocked_locks.relation = blocking_locks.relation
JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Expected output: who blocks whom and on what query. A cycle in this graph is your deadlock.

3. Impose a consistent lock order. The classic fix is ordering row locks by primary key:

BEGIN;
SELECT * FROM accounts WHERE id IN (7, 3, 9) ORDER BY id FOR UPDATE;
-- now do the updates; every transaction takes these rows in id order
UPDATE accounts SET balance = balance - 100 WHERE id = 3;
UPDATE accounts SET balance = balance + 100 WHERE id = 7;
COMMIT;

Expected output: no deadlock, because every concurrent transaction locks rows 3, 7, 9 in the same sequence and one simply waits for the other.

4. Shrink the transaction: move non-database work outside it and commit promptly:

# bad: HTTP call inside the transaction holds locks for seconds
# good:
result = call_external_api()  # outside
with db.transaction():
    db.execute("UPDATE accounts SET ...")  # fast, then commit

Expected output: shorter lock hold times, which shrinks the window where two transactions can interleave badly.

5. Retry the victim with backoff in application code. Deadlocks are expected concurrency events, not bugs:

import time, random
for attempt in range(4):
    try:
        run_transfer()
        break
    except DeadlockError:
        time.sleep(0.1 * (2 ** attempt) + random.random() * 0.1)
else:
    raise

Expected output: transient deadlocks resolve on retry. If the same transaction deadlocks repeatedly, the lock order is still inconsistent, go back to step 3.

Variant phrasings

postgres deadlock detected how to debug

Steps 1-2 give you the who and what. The log DETAIL is the single most useful artifact.

deadlock detected process waits for ShareLock

The standard DETAIL format. Two processes each hold a lock the other wants; Postgres picks a victim and aborts it so the other can proceed.

how to avoid deadlocks in postgres

Consistent lock ordering (step 3), short transactions (step 4), and retry logic (step 5). In that order of importance.

Why it happens

Row locks are held until transaction end, and two transactions that lock the same set of rows in different orders can each end up holding a lock the other needs. Neither can proceed, so Postgres detects the wait cycle and aborts one participant to break it. It is a scheduling problem, not data corruption: the aborted transaction simply needs to run again.

Edge cases

• Foreign keys take locks on the referenced row too; inserting child rows concurrently can deadlock on the parent. Order parent touches consistently as well.
• Unique-index inserts can deadlock via speculative insertion; retry logic covers these.
• SELECT ... FOR UPDATE without ORDER BY locks in scan order, which varies; always add ORDER BY on a unique key.
• Serializable isolation raises serialization failures instead of deadlocks sometimes; the retry pattern from step 5 covers both.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_TpNg6JjSXpEkI5zvhCK0IQ

Resolved from the public thread: https://vectle.com/posts/pst_TpNg6JjSXpEkI5zvhCK0IQ

Thread query: "deadlock detected" Postgres: what causes it

TL;DR: Two transactions locked the same rows in opposite order, so each waits on the other forever and Postgres aborts one. Fix it by always locking rows in the same order (e.g. ORDER BY primary key in SELECT ... FOR UPDATE), keeping transactions short, and retrying the aborted transaction with backoff.