Intermediate18 min read

Transactions

Ensure data consistency with transactions, isolation levels, and deadlock handling.

Transaction Basics

Transactions group operations into atomic units—either all commit or all roll back. InnoDB supports ACID transactions; MyISAM does not. Always use InnoDB for transactional tables.

Start transactions explicitly with START TRANSACTION or BEGIN. Autocommit mode (default) wraps each statement in its own transaction.

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

COMMIT and ROLLBACK

COMMIT persists changes; ROLLBACK discards changes since transaction start. Use SAVEPOINT for partial rollback within a transaction.

Keep transactions short to reduce lock duration. Long transactions block other writers and readers depending on isolation level, causing contention and deadlocks.

Application code should rollback on exceptions before returning connections to pool
DDL statements may implicit commit—avoid mixing DDL in business transactions
Use SELECT ... FOR UPDATE when reading rows you will update in same transaction

SAVEPOINT before_transfer;
-- if error
ROLLBACK TO SAVEPOINT before_transfer;

Isolation Levels

READ UNCOMMITTED allows dirty reads. READ COMMITTED prevents dirty reads. REPEATABLE READ is MySQL InnoDB default and prevents non-repeatable reads with next-key locking. SERIALIZABLE is strictest and slowest.

Understand phantom reads and gap locks in REPEATABLE READ when designing concurrent inventory or seat booking logic.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- operations
COMMIT;

Deadlock Prevention

Deadlocks occur when transactions lock rows in conflicting order. InnoDB detects deadlocks and rolls back one transaction automatically, returning error 1213.

Access rows in consistent order across transactions (always lock account id ascending). Retry deadlocked transactions with exponential backoff in application code.

-- Consistent lock order prevents many deadlocks
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

Application Patterns

ORMs map transactions to unit-of-work boundaries—one transaction per HTTP request is common but not mandatory. For sagas spanning services, use outbox tables and eventual consistency instead of distributed two-phase commit.

Monitor innodb_lock_wait_timeout and deadlock metrics. Log transaction duration to catch unexpectedly slow business logic holding locks.