MySQL Transaction Patterns: Short Transactions and Retries

Knowing how transactions and locks work is half the job. The other half is a handful of patterns you use again and again.

Pattern 1: begin, work, commit, and roll back on any error

In PHP with PDO:

index.php

PHP

<?php
$pdo->beginTransaction();
try {
    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?')->execute([$amount, $from, $amount]);
    if ($pdo->query('SELECT ROW_COUNT()')->fetchColumn() < 1) {
        throw new RuntimeException('Insufficient funds');
    }
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([$amount, $to]);
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

The guard AND balance >= ? and the row count check make the debit atomic and safe: two concurrent transfers cannot overdraw the account.

Pattern 2: do slow work outside the transaction

A transaction should hold locks for milliseconds. Do not call an HTTP API, send an email, or wait for the user inside it:

Text

PLAIN

1
2

BAD:   begin -> charge the payment API (2 seconds) -> update order -> commit
GOOD:  charge the payment API -> begin -> update order -> commit

If the external call must be tied to the transaction, use a two-step flow: record the intention in the database, make the call, then record the result. The pattern is called an outbox: write the “send this email” job in the same transaction as the order, and let a worker send it afterwards.

query.sql

SQL

CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY, customer VARCHAR(20) NOT NULL, total DECIMAL(8,2) NOT NULL) ENGINE = InnoDB;
CREATE TABLE outbox (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(30) NOT NULL,
    payload VARCHAR(100) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at DATETIME NULL,
    KEY idx_unprocessed (processed_at, id)
) ENGINE = InnoDB;

START TRANSACTION;
INSERT INTO orders (customer, total) VALUES ('Maya', 480.00);
INSERT INTO outbox (event_type, payload) VALUES ('order_created', CONCAT('order_id=', LAST_INSERT_ID()));
COMMIT;

SELECT id, event_type, payload, processed_at FROM outbox;

Output

OUTPUT

+----+---------------+------------+--------------+
| id | event_type    | payload    | processed_at |
+----+---------------+------------+--------------+
|  1 | order_created | order_id=1 | NULL         |
+----+---------------+------------+--------------+
1 row in set

The order and its “email this” event are saved atomically. A separate worker reads unprocessed events, performs the side effect and marks them processed. If the worker crashes, the event is still there. This avoids the “paid but no confirmation” and “email sent but order lost” problems.

Pattern 3: idempotency with a unique key

Networks fail and clients retry. If a request can be sent twice, the second one must not do the work again. Give each logical operation a unique key and let the database enforce it:

query.sql

SQL

CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    idempotency_key VARCHAR(40) NOT NULL,
    order_id INT NOT NULL,
    amount DECIMAL(8,2) NOT NULL,
    UNIQUE KEY uq_idempotency (idempotency_key)
) ENGINE = InnoDB;

INSERT INTO payments (idempotency_key, order_id, amount) VALUES ('req-7f3a', 1, 480.00);
INSERT INTO payments (idempotency_key, order_id, amount) VALUES ('req-7f3a', 1, 480.00);

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry 'req-7f3a' for key 'payments.uq_idempotency'

The second request, with the same key, is rejected by the UNIQUE index. The application catches error 1062, finds the original payment, and returns the same result: the retry is harmless. The alternative INSERT ... ON DUPLICATE KEY UPDATE or INSERT IGNORE can express “do it once” (Upsert).

Pattern 4: a job queue with SKIP LOCKED

Several workers must take different jobs from a table. FOR UPDATE SKIP LOCKED (MySQL 8.0) makes this safe and simple:

query.sql

SQL

CREATE TABLE jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payload VARCHAR(20) NOT NULL,
    status VARCHAR(10) NOT NULL DEFAULT 'new',
    KEY idx_status_id (status, id)
) ENGINE = InnoDB;
INSERT INTO jobs (payload) VALUES ('j1'), ('j2'), ('j3'), ('j4');

Each worker runs:

query.sql

SQL

START TRANSACTION;
SELECT id, payload FROM jobs WHERE status = 'new' ORDER BY id LIMIT 2 FOR UPDATE SKIP LOCKED;
-- ... process the jobs ...
UPDATE jobs SET status = 'done' WHERE id IN (...);
COMMIT;

Two workers started at the same time, as a real run shows:

Output

OUTPUT

-- worker A gets:          -- worker B gets (while A's transaction is open):
+----+---------+            +----+---------+
| id | payload |            | id | payload |
+----+---------+            +----+---------+
|  1 | j1      |            |  3 | j3      |
|  2 | j2      |            |  4 | j4      |
+----+---------+            +----+---------+

Worker B skipped the rows locked by A, so each job is taken by exactly one worker, with no waiting. For long-running jobs, do not hold the transaction open: mark the job processing with a timestamp in a short transaction, commit, process, then mark done. A sweeper resets jobs stuck in processing for too long.

query.sql

SQL

START TRANSACTION;
SELECT id FROM jobs WHERE status = 'new' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
UPDATE jobs SET status = 'processing' WHERE id = 1;
COMMIT;
SELECT id, status FROM jobs ORDER BY id;

Output

OUTPUT

+----+
| id |
+----+
|  1 |
+----+
1 row in set

+----+------------+
| id | status     |
+----+------------+
|  1 | processing |
|  2 | new        |
|  3 | new        |
|  4 | new        |
+----+------------+
4 rows in set

Pattern 5: the optimistic version check

For edit forms where users think for minutes, do not lock. Add a version column and make the update conditional (Updating Under Concurrency):

query.sql

SQL

CREATE TABLE documents (id INT PRIMARY KEY, body VARCHAR(50) NOT NULL, version INT NOT NULL DEFAULT 1) ENGINE = InnoDB;
INSERT INTO documents VALUES (1, 'first draft', 1);

UPDATE documents SET body = 'second draft', version = version + 1 WHERE id = 1 AND version = 1;
SELECT ROW_COUNT() AS saved_first_time;
UPDATE documents SET body = 'conflicting draft', version = version + 1 WHERE id = 1 AND version = 1;
SELECT ROW_COUNT() AS saved_second_time;

Output

OUTPUT

+------------------+
| saved_first_time |
+------------------+
|                1 |
+------------------+
1 row in set

+-------------------+
| saved_second_time |
+-------------------+
|                 0 |
+-------------------+
1 row in set

The second save used a stale version, so no row matched: the application reports the conflict instead of overwriting.

Pattern 6: retry the whole transaction on deadlock

Wrap business transactions in a function that retries on errors 1213 and 1205, as shown in Deadlocks.

Pattern 7: batch in small transactions

For bulk updates and deletes, commit every few thousand rows (Changing Millions of Rows), so locks and undo stay small.

Pattern 8: lock in a fixed order

When a transaction touches several rows, lock them in a consistent order (by primary key), so two transactions cannot deadlock on each other.

Checklist before shipping a transaction

[ ] Does it need to be a transaction? (Several statements that must succeed together)
[ ] Is it short? No user waits, no network calls inside.
[ ] Is the error path rolled back, and the connection returned clean to the pool?
[ ] Are retryable errors (1213, 1205) handled?
[ ] Is it idempotent, or protected by a unique key?
[ ] Are rows locked in a consistent order?
[ ] Is every WHERE backed by an index?
[ ] Was it tested with two sessions at once?

lightbulb

Test concurrency on purpose

Open two mysql windows and run your transaction steps interleaved, as in this chapter. Ten minutes of manual testing finds problems that unit tests never will.