Avoiding InnoDB sequence deadlocks under concurrency

Many systems need a monotonic sequence inside a partition: event number per task, audit number per entity, attempt number per job. The obvious SQL reads the current max, adds one, and inserts the next row. Under InnoDB concurrency, the locking details matter.

The tempting query

SELECT COALESCE(MAX(sequence_no), 0) + 1 AS next_sequence
FROM task_events
WHERE task_id = ?
FOR UPDATE;

In a transaction under the default repeatable-read isolation, this can take gap locks on the relevant index range. When multiple writers allocate a sequence for the same partition, they can deadlock before any useful work happens.

Let the unique key arbitrate

A better pattern is optimistic. Use read committed isolation, compute the next sequence without FOR UPDATE, insert with a unique constraint on (partition_id, sequence_no), and retry the transaction if another writer won the same number first.

CREATE UNIQUE INDEX task_events_sequence
ON task_events (task_id, sequence_no);

for attempt := 0; attempt < 10; attempt++ {
    tx := beginReadCommitted()
    next := selectMaxPlusOne(tx, taskID)
    err := insertEvent(tx, taskID, next, payload)
    if err == nil {
        return tx.Commit()
    }
    tx.Rollback()
    if !isDuplicateKey(err) {
        return err
    }
}

Why this is correct

The unique constraint is the source of truth. Two writers may compute the same next number, but only one can commit it. The loser retries, sees a new max, and inserts the following number. Correctness is enforced by the database, while throughput avoids unnecessary gap locks.

Where to use it

Apply this to per-entity event logs, attempt counters, audit trails, and ordered child records. Do not use it when you need a single global sequence; databases already provide auto-increment or sequence objects for that. The pattern is specifically for monotonic ordering inside a partition key.

Low-concurrency tests rarely reveal this issue. Load tests with many writers on the same partition are the useful proof.