PHP PDO Transactions, Replay, and Query Identity

Working case

Reviewer 47 approves permit 447 at revision 8. The database commits revision 9, but the browser connection closes before the response. The browser retries; without a replay key it creates two audit rows and two email intents. Meanwhile reviewer 81 tries to approve the same permit based on revision 8. A transaction inserts one request identity, updates the permit only if its revision matches, appends one audit event and one outbox intent, then commits. The retry reads the saved outcome. Reviewer 81 gets a conflict rather than a successful overwrite. A worker sends the email from the outbox after commit and handles duplicate delivery with its own identity.

Implementation boundary

<?php
$statement = $database->prepare(
    'UPDATE permits SET status = ?, revision = revision + 1 '
    . 'WHERE permit_id = ? AND revision = ?'
);
$statement->execute(['approved', 447, 8]);
if ($statement->rowCount() !== 1) {
    throw new RuntimeException('revision_conflict');
}

Use an allowlisted SQL shape and bind user data as values. Set PDO to throw exceptions and manage begin, commit, and rollback in one service boundary. Give the replay table a unique constraint on actor, route, and request key; store a request digest to detect reuse of the same key with different input. Update with a predicate on permit ID and expected revision, then inspect affected rows. Insert the audit event and outbox row only after a successful versioned update and before commit. On an exception, roll back if the transaction is active; do not turn every database error into a safe retry. Avoid performing an HTTP call while a database transaction holds locks.

Cost and boundaries

A keyed lookup or uniqueness check is usually O(log N) with a database index, while the transaction also pays log writes and lock time for each mutation. The version predicate adds little query cost when permit ID is indexed, but conflicting writers must retry with fresh state. An outbox introduces storage and a worker scan, trading that cost for recoverable side effects. Long transactions increase lock contention and can reduce throughput. Keep replay records for a documented window; deleting them too soon permits late duplicate effects. Measure commit latency, conflict rate, replay hit rate, outbox age, and delivery attempts separately from HTTP success count.

Failure trace

Send one approval with a fixed request key, sever the response after commit, then resend the exact body and key. Only one revision increment, audit event, and outbox intent may exist. Reuse that key with a different permit or decision and require a conflict. Race two reviewers with expected revision 8; only one update may succeed. Inject a database failure after the permit update but before the outbox insert, and verify rollback removes both changes. Make the email worker fail after sending but before marking done; its retry needs a stable delivery identity and a reconciliation policy rather than assuming exactly-once network delivery.

Verification

A repeated command creates one local effect.
A stale revision conflicts without an audit or outbox row.
A transaction failure removes every partial local write.

Practice drill

Create SQLite tables for permits, replay keys, audit events, and outbox intents. Start permit 447 at revision 8 and status pending. Implement an approve command with actor 47, expected revision, and a caller-generated key. Run it twice with identical input, once with a changed body under the same key, and once concurrently with another actor. Query all four tables after every case. Add a test that throws before commit. The endpoint should distinguish replay, version conflict, invalid key reuse, and database failure in its response contract.

Decision note

A database transaction owns local state; durable intent and replay identity bridge uncertain network boundaries.

Common Mistakes

Using placeholders for SQL identifiers.
Calling an email service inside a database transaction.
Retrying an approval with a new identity after an uncertain response.

Apply and check

Build Project: PHP Permit Review Intake and review Web Development: PHP Request Contracts.