Make read-once links race-safe with MySQL

Two reveal requests can arrive at the same instant. A plain SELECT followed by DELETE does not ensure that only one returns the ciphertext: both requests may read it first. A short transaction with a locking read makes consumption a single serialized decision.

This integration recipe targets MySQL 8.4 InnoDB with mysql2's promise API. It requires an existing validated token-hash lookup and a POST-only reveal route. A MySQL server was not available in this tutorial environment; the two-session test below is a reproducible acceptance procedure, not a claimed execution result.

Choose at-most-once semantics

The policy here is one successful server claim. Commit consumption before returning the ciphertext. If the connection drops after commit, the next request fails even if the recipient saw nothing.

That tradeoff is unavoidable in this simple protocol: the server cannot atomically commit database state and prove that a remote browser received and displayed the response. Keeping a replayable response can improve retry reliability, but it weakens strict one-time retrieval and needs a separate authenticated request identity.

Define a tombstone-capable record

CREATE TABLE one_time_secrets (
  token_hash BINARY(32) PRIMARY KEY,
  envelope JSON NULL,
  expires_at DATETIME(6) NOT NULL,
  consumed_at DATETIME(6) NULL
) ENGINE=InnoDB;

The envelope contains only ciphertext, IV and version metadata. The decryption key stays in the browser link fragment. Generate the lookup token independently of the encryption key, validate its encoding and hash it consistently.

Keeping a small consumed record can simplify operations, but its retention should be bounded. Clearing the envelope is logical removal, not proof that every backup or storage page has been erased.

Lock, validate, consume, commit

All statements must use the same connection. Do not call the pool independently for each query and assume they share a transaction.

export async function consume(pool, tokenHash) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    const [rows] = await connection.execute(
      "SELECT envelope FROM one_time_secrets " +
      "WHERE token_hash = ? AND consumed_at IS NULL " +
      "AND expires_at > UTC_TIMESTAMP(6) FOR UPDATE",
      [tokenHash]
    );
    if (rows.length !== 1) {
      await connection.rollback();
      return null;
    }
    const envelope = rows[0].envelope;
    await connection.execute(
      "UPDATE one_time_secrets " +
      "SET consumed_at = UTC_TIMESTAMP(6), envelope = NULL " +
      "WHERE token_hash = ?",
      [tokenHash]
    );
    await connection.commit();
    return envelope;
  } catch (error) {
    await connection.rollback().catch(() => {});
    throw error;
  } finally {
    connection.release();
  }
}

The row lock is held until transaction completion. The next contender waits and then observes the consumed state. A primary-key lookup keeps the lock scope focused. Do not add network calls, message decryption or slow logging while holding the transaction. MySQL locking reads.

If secrets are account-bound, include the verified account constraint in the locking query. Authorization must happen before consumption, not after revealing the envelope.

Handle errors without inventing certainty

A known deadlock rollback can be retried with a bounded policy. An ambiguous connection failure during commit is different: the server may have committed. Do not treat every database error as proof that retrying is harmless.

MySQL recommends applications be prepared to handle deadlocks. Keep transactions short, use consistent lock ordering and record sanitized failure classifications. MySQL deadlock handling.

The HTTP route maps a null result to a generic unavailable response and successful retrieval to a no-store JSON response. Never send the ciphertext before commit. Never log the envelope, raw lookup token or fragment.

Reproduce the race

Seed one future-expiring synthetic record. In terminal A, start a transaction and run the locking SELECT. In terminal B, run the same transaction and SELECT; it should block. In A, clear the envelope, set consumed_at and commit. B should then receive no eligible record and roll back.

Next run two simultaneous HTTP POST requests against the integrated route with separate connections. Expect one successful response and one unavailable response. Repeat with an expired record, an unknown token and a forced disconnect immediately after commit. The disconnected success must remain consumed.

If both requests return ciphertext, look for reads outside the transaction, different database instances or a non-InnoDB table. If callers hang, inspect uncommitted connections and lock timeouts.

Pair this with preview-safe landing pages. CodeKits can help review the transaction and delivery contract in a Next.js application.