Retry Safe Writes: Idempotency Keys In Laravel And MySQL 8

A checkout endpoint on Laravel 10.43, PHP 8.2.19, MySQL 8.0.36, running on three FPM pods behind an ALB with 30s idle timeout. Average order value write is one INSERT into orders plus an INSERT into order_items. At ~40 req/s during a flash sale, PDO threw SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded on a row we had just inserted. Retrying the request naively created a second order: same customer, same cart, two charges. That is the concrete problem: a network retry or a client retry must not create a duplicate side effect.

The fix I use is an idempotency key stored in its own table, claimed inside the same transaction as the write. The client sends a UUID in Idempotency-Key; if it doesn’t, the API rejects the request rather than pretend it is safe.

CREATE TABLE idempotency_keys (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  idem_key CHAR(36) NOT NULL,
  request_hash CHAR(64) NOT NULL,
  status TINYINT NOT NULL DEFAULT 0, -- 0 claimed, 1 done
  response_body JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_key (idem_key)
) ENGINE=InnoDB;

DB::transaction(function () use ($key, $payload) {
    $hash = hash('sha256', json_encode($payload));
    DB::insert(
        'INSERT INTO idempotency_keys (idem_key, request_hash) VALUES (?, ?)',
        [$key, $hash]
    ); // duplicate key here means a retry, not a new request

    $order = Order::create($payload);
    DB::table('idempotency_keys')
        ->where('idem_key', $key)
        ->update(['status' => 1, 'response_body' => json_encode($order->toArray())]);
    return $order;
});

On a duplicate-key exception, catch QueryException with SQLSTATE 23000 and read the stored row. If request_hash differs, return 422: the same key was reused with a different body, which is a client bug. If status is 0, another transaction is still in flight; return 409 and let the client retry with backoff. If status is 1, replay response_body and return 200.

The mechanism is the unique index, not application logic. InnoDB serializes the insert on the key, so two concurrent requests cannot both proceed. The cost is one extra row and one extra round trip per write, plus a transaction that now holds locks on two tables. Keep the transaction short: do not call external payment APIs inside it. I put the payment call after commit and update the key row on callback, accepting that the key can sit in claimed state for a few seconds.

Where it breaks. First, MySQL 8.0 with READ COMMITTED: the second transaction blocks until the first commits, which is what you want, but under REPEATABLE READ a long first transaction can push the second past innodb_lock_wait_timeout (default 50s). Set it to 5s for the API and return 409. Second, this does nothing for retries that arrive with a fresh key each time; if your client generates the key inside its own retry loop, every attempt is new. Generate once at the outermost call site. Third, the table grows forever. I partition by month and drop partitions older than 7 days, which is longer than any client retry window I have seen. Fourth, a crash between commit and the status = 1 update leaves a claimed row; a sweeper that marks rows older than the lock timeout as failed is required, otherwise clients get 409 forever.

What is your cutoff for keeping idempotency rows, and do you store the response body or just a reference to the created record?