Database

Choose Transactions and Isolation Levels Deliberately

Use transaction boundaries and isolation semantics to protect business invariants without creating avoidable contention.

Overview

Transactions group related database changes into one logical unit. They are essential when an operation must not be partially applied, but long or overly broad transactions can increase lock duration, contention, and deadlocks.

Start with the workload and the invariant

Define the invariant first. For example, transferring funds requires debiting one account and crediting another as one unit. Both updates must commit or both must roll back. Keep validation, network calls, email delivery, and slow file operations outside the transaction whenever possible. The transaction should contain only the database work required to preserve consistency.

Practical example

Isolation levels control which concurrent changes a transaction can observe. The exact behavior depends on the engine and server version. InnoDB commonly uses REPEATABLE READ by default in MySQL, while applications may choose READ COMMITTED. Do not select an isolation level by folklore: document which anomalies the business operation can tolerate and test concurrent execution.

START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE id = 10 AND balance >= 100;

-- Application must verify that exactly one row was affected.
UPDATE accounts
SET balance = balance + 100
WHERE id = 20;

COMMIT;

Trade-offs and failure modes

The example is incomplete without checking both affected-row counts and handling errors. If the debit did not affect exactly one row, roll back and do not credit the destination. For operations involving multiple rows, acquire locks in a consistent order to reduce deadlocks. Use SELECT ... FOR UPDATE only when a read-modify-write sequence truly needs row locks.

Verification and rollout

Deadlocks are a normal possibility in concurrent systems. Catch the relevant database error and retry the entire transaction a small, bounded number of times with jitter. Never retry only the last statement: the transaction's earlier assumptions may no longer hold. Make external side effects idempotent or publish them after commit through an outbox pattern.

Engineering checklist

Test races with parallel workers, not only sequential unit tests. Include insufficient balance, duplicate requests, timeout, deadlock, and process termination scenarios. Transaction correctness is about preserving invariants under failure and concurrency, not simply placing BEGIN and COMMIT around several statements.

Before shipping, review the database version, storage engine, SQL mode, character set, collation, and deployment process used by the target environment. Features and DDL behavior can differ between MySQL and MariaDB releases. Validate the exact statements on a representative staging database, capture a baseline, and keep a tested recovery path for changes that affect production data.

Share this page Help other developers discover this resource.