Reliable Asynchronous Writes with MySQL

Moving a database write out of an HTTP request can make the response faster, but it also changes the failure model. The important question is not how to make an INSERT return in the background. It is when the application may truthfully tell the caller that the operation succeeded.

For authoritative state such as an order, payment, permission change, or inventory reservation, success normally means that MySQL has committed the transaction. Email, search indexing, analytics, cache invalidation, and webhook delivery can often happen later. Reliable asynchronous writes begin by separating those two classes of work.

Two Clocks, Two Guarantees

Request latency measures when the application can return a response. Durability describes when a committed change survives the process that initiated it. Returning before a database acknowledges a write reduces request latency, but it does not make the write durable.

That distinction gives three useful delivery contracts:

• Synchronous commit: return success only after the authoritative transaction commits.
• Best-effort delivery: enqueue or buffer data that may be lost, such as low-value diagnostics.
• Durable asynchronous delivery: commit an intent now and let a worker execute it later with retries.

Choose the Smallest Correct Design

Putting a queue in front of every write is not automatically faster. It adds serialization, storage, scheduling, and another failure boundary. Start with a direct transaction and add asynchronous processing only where the product can tolerate eventual completion or where an independent side effect must follow a committed change.

Why Client-Side Async Queries Are Not a Queue

PHP's MySQLi extension can submit a query with MYSQLI_ASYNC. This is useful when one process has several independent queries to run in parallel. The process still has to poll the connections and call reap_async_query() to learn whether each operation succeeded. Concurrent queries also require separate connections while results are outstanding.

If the request process exits after dispatching a query, the application has no durable job record, acknowledgement, retry schedule, or idempotency key. The server may have committed the statement, rejected it, or lost the connection before receiving it. MYSQLI_ASYNC overlaps client wait time; it does not make the write durable and it does not detach responsibility from the request.

The same limitation applies to forking a PHP child, keeping a queue only in APC/shared memory, or appending raw SQL strings to an unmanaged file. Each technique can move work, but none supplies a complete delivery protocol by itself. A durable design needs explicit ownership, acknowledgement, retry, deduplication, and overload behavior.

The Dual-Write Failure

Suppose an order transaction commits and the application then publishes an order.created event. A crash between those operations leaves an order with no event. Reversing the order is no better: the event may be delivered even though the database transaction later rolls back.

The transactional outbox solves this dual-write problem by storing the business change and a durable description of the follow-up work in the same InnoDB transaction. A separate worker only sees committed outbox rows. The database remains the source of truth, while delivery can proceed independently of request latency.

Build the Outbox

A compact outbox table needs enough state for claiming, retries, deduplication, and diagnosis:

CREATE TABLE outbox (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  topic VARCHAR(100) NOT NULL,
  aggregate_id VARCHAR(100) NOT NULL,
  payload JSON NOT NULL,
  idempotency_key BINARY(16) NOT NULL,
  status ENUM('pending', 'processing', 'published', 'failed')
    NOT NULL DEFAULT 'pending',
  available_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  claimed_at DATETIME(6) NULL,
  claimed_by BINARY(16) NULL,
  attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  last_error VARCHAR(1000) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  published_at DATETIME(6) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_outbox_idempotency (idempotency_key),
  KEY ix_outbox_claim (status, available_at, id),
  KEY ix_outbox_worker (claimed_by, status)
) ENGINE = InnoDB;

The payload should describe an operation or event, not contain executable SQL. Structured data keeps schema changes, authorization, validation, and retry policy in application code instead of turning the queue into a delayed SQL injection boundary.

Commit State and Intent Together

The producer writes both rows through one connection and one transaction. This PHP example uses PDO, prepared statements, a binary idempotency key, and an explicit rollback path:

$payload = json_encode([
    'order_id' => $orderId,
    'customer_id' => $customerId,
], JSON_THROW_ON_ERROR);

$eventKey = random_bytes(16);
$pdo->beginTransaction();

try {
    $order = $pdo->prepare(
        'INSERT INTO orders (id, customer_id, status) VALUES (?, ?, ?)'
    );
    $order->execute([$orderId, $customerId, 'accepted']);

    $event = $pdo->prepare(
        'INSERT INTO outbox
         (topic, aggregate_id, payload, idempotency_key)
         VALUES (?, ?, ?, ?)'
    );
    $event->execute(['order.created', $orderId, $payload, $eventKey]);

    $pdo->commit();
} catch (Throwable $error) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $error;
}

After commit() succeeds, the order and the pending event either both exist or neither exists. The HTTP response can now report that the order was accepted without waiting for email, analytics, or another service.

Claim Work without Holding Locks during I/O

Several workers can claim different rows with an indexed FOR UPDATE SKIP LOCKED query. Select a small batch, mark it with a unique worker token, and commit immediately:

START TRANSACTION;

SELECT id
FROM outbox
WHERE status = 'pending'
  AND available_at <= NOW(6)
ORDER BY id
LIMIT 100
FOR UPDATE SKIP LOCKED;

UPDATE outbox
SET status = 'processing',
    claimed_at = NOW(6),
    claimed_by = :worker_token,
    attempts = attempts + 1
WHERE id IN (101, 102, 103);

COMMIT;

The IDs in the UPDATE are the rows selected by that transaction. The worker performs network calls only after COMMIT, so a slow broker or webhook does not keep InnoDB row locks open. Once delivery succeeds, it marks the row as published:

UPDATE outbox
SET status = 'published',
    published_at = NOW(6),
    claimed_at = NULL,
    claimed_by = NULL,
    last_error = NULL
WHERE id = :id
  AND claimed_by = :worker_token;

MySQL documents that SKIP LOCKED returns an inconsistent view and is unsuitable for general transactional reads. That behavior is intentional here: each worker wants any currently available jobs, not a complete snapshot of the queue. It also means global processing order is not guaranteed. If events for one aggregate must remain ordered, store a sequence number and prevent a later event for that aggregate from overtaking an earlier one.

Design for At-Least-Once Delivery

A worker can crash after publishing an event but before marking its outbox row as published. The row will be retried, so the practical contract is at-least-once delivery. The consumer must treat the idempotency_key as a unique operation identifier and record it in the same transaction as the consumer's own state change. A duplicate then becomes a successful no-op.

A lease recovers jobs abandoned by dead workers. Its duration must exceed the normal processing time, or the worker must renew it while handling long operations:

UPDATE outbox
SET status = 'pending',
    available_at = DATE_ADD(NOW(6), INTERVAL 30 SECOND),
    claimed_at = NULL,
    claimed_by = NULL
WHERE status = 'processing'
  AND claimed_at < NOW(6) - INTERVAL 5 MINUTE;

On transient failure, clear the claim and calculate the next available_at with capped exponential backoff plus random jitter. Jitter prevents every failed job from retrying at the same instant. After a configured attempt limit, change the status to failed and expose the row through a dead-letter review process. Do not retry permanent validation or authorization failures indefinitely.

Batching without Hiding Failure

Batching reduces connection, round-trip, and commit overhead. Claim tens or hundreds of rows at a time, use a broker's batch API or a prepared multi-row insert where appropriate, and keep the batch bounded. Very large transactions hold locks longer, produce larger rollback work, and make one bad item expensive to isolate.

Batch data, not raw query strings. Preserve one idempotency key and one result per logical operation even when several operations share a transport call. If the destination reports partial success, acknowledge only the successful items and retry the rest.

Backpressure Is Part of Correctness

Backpressure defines what happens when producers are faster than workers. An unbounded queue merely converts a latency incident into disk exhaustion. Set capacity and age limits, scale workers within the database's connection budget, and decide explicitly whether to reject new work, degrade optional features, sample telemetry, or fall back to a synchronous path.

At minimum, monitor queue depth, the age of the oldest pending job, publish latency, throughput, retry counts, failure rate, dead-letter volume, database lock waits, and connection saturation. Queue depth alone is insufficient: a stable number of old jobs can be worse than a larger queue that drains continuously.

When MySQL Should Stop Being the Queue

An InnoDB outbox is attractive because it shares the transaction boundary already required by the application. It is not a replacement for every messaging system. Move delivery behind a dedicated message broker when routing, fan-out, consumer groups, long retention, delayed messages, cross-region replication, or independent scaling become central requirements. Keep the outbox at the producer boundary if the database update and event still need one atomic commit.

The durable architecture is therefore deliberately unsurprising: commit authoritative state synchronously, record follow-up intent in the same transaction, let workers claim short leases, assume duplicates, make consumers idempotent, and observe backlog age as carefully as request latency.

References

• [MySQLTransactions]Oracle. MySQL 8.4 Reference Manual: START TRANSACTION, COMMIT, and ROLLBACK Statements.
• [MySQLLockingReads]Oracle. MySQL 8.4 Reference Manual: Locking Reads.
• [PHPMySQLi]PHP Documentation Group. PHP Manual: mysqli::poll and mysqli::reap_async_query.
• [AWSOutbox]Amazon Web Services. AWS Prescriptive Guidance: Transactional outbox pattern.