Transaction Guide

Basic Transaction Control

Start, commit, and roll back transactions using explicit control statements.

-- Explicit transaction
START TRANSACTION;   -- or BEGIN;

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

COMMIT;  -- persist changes

-- Rollback on error
START TRANSACTION;
UPDATE inventory SET qty = qty - 1 WHERE product_id = 5;

-- Simulate error condition
ROLLBACK;  -- undo all changes in this transaction

-- Check autocommit setting
SHOW VARIABLES LIKE 'autocommit';
SET autocommit = 0;  -- disable for session

Isolation Levels

MySQL InnoDB supports four transaction isolation levels controlling read visibility.

-- View current isolation level
SELECT @@transaction_isolation;

-- Set for current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set globally
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Set for next transaction only
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Available levels:
-- READ UNCOMMITTED  – dirty reads allowed (fastest, least safe)
-- READ COMMITTED    – no dirty reads; phantom reads possible
-- REPEATABLE READ   – default; consistent snapshot per transaction
-- SERIALIZABLE      – fully isolated; uses locking (slowest)

Savepoints

Savepoints allow partial rollbacks within a transaction.

START TRANSACTION;

INSERT INTO orders (user_id, total) VALUES (1, 250.00);
SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 5, 2);
SAVEPOINT after_items;

-- Something goes wrong with payment
ROLLBACK TO SAVEPOINT after_order;
-- order_items insert is undone; orders insert is kept

-- Continue or commit
COMMIT;

-- Release a savepoint (optional)
RELEASE SAVEPOINT after_order;

Deadlock Prevention

Deadlocks occur when two transactions wait for each other's locks. Use consistent ordering and short transactions.

-- Deadlock example (avoid this pattern):
-- Transaction A: locks row 1, then tries row 2
-- Transaction B: locks row 2, then tries row 1

-- Prevention: always lock rows in the same order
-- Transaction A and B both lock lower id first:
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
COMMIT;

-- Use SELECT ... FOR UPDATE to acquire row locks early
START TRANSACTION;
SELECT balance FROM accounts WHERE id = ? FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = ?;
COMMIT;

-- Detect deadlocks
SHOW ENGINE INNODB STATUS\G  -- look for LATEST DETECTED DEADLOCK

-- innodb_deadlock_detect (MySQL 8.0): enabled by default
-- Set innodb_lock_wait_timeout for automatic resolution (default 50s)
SET GLOBAL innodb_lock_wait_timeout = 10;

Locking Reads

-- Shared lock (other sessions can also read but not write)
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;

-- Exclusive lock (no other session can read or write)
SELECT * FROM products WHERE id = 1 FOR UPDATE;

-- MySQL 8.0: NOWAIT and SKIP LOCKED
SELECT * FROM jobs WHERE status = 'pending' LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Useful for queue processing: skip rows locked by other workers

SELECT * FROM queue WHERE id = 42
FOR UPDATE NOWAIT;
-- Fails immediately if row is locked