Transaction Isolation Levels in InnoDB Explained

I've read about isolation levels a dozen times and still can't recite them, so I learn them by running two terminals side by side. It matters because InnoDB's default, REPEATABLE READ, doesn't behave like the same-named level elsewhere, and its gap locks are behind a good share of the deadlocks and "why is this UPDATE waiting" tickets I get. Everything below works on MySQL 5.7 and 8.0 and MariaDB 10.x; only a variable name differs.

Open two mysql sessions to the same server (A and B) and create a small test table:

CREATE TABLE accounts (id INT PRIMARY KEY, balance INT NOT NULL, KEY (balance)) ENGINE=InnoDB;
INSERT INTO accounts VALUES (1,100),(2,200),(3,300),(10,1000);

Checking and setting the level

SHOW VARIABLES LIKE 'transaction_isolation';   -- 5.7.20+ and 8.0
SHOW VARIABLES LIKE 'tx_isolation';            -- older 5.7 and MariaDB
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

To change the server default put transaction_isolation = READ-COMMITTED (with a hyphen) in the [mysqld] section. A per-transaction override is SET TRANSACTION ISOLATION LEVEL ... immediately before START TRANSACTION.

READ UNCOMMITTED: dirty reads

-- A
START TRANSACTION; UPDATE accounts SET balance = 0 WHERE id = 1;
-- B
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE id = 1;     -- returns 0, uncommitted
-- A
ROLLBACK;

B saw data that never existed. There is almost no legitimate use for this level; some people use it for rough progress counts on huge batch jobs, and that is about it.

READ COMMITTED: each statement sees the latest commit

-- B
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 2;     -- 200
-- A
UPDATE accounts SET balance = 250 WHERE id = 2;   -- autocommit
-- B
SELECT balance FROM accounts WHERE id = 2;     -- 250, non-repeatable read
COMMIT;

Each SELECT gets a fresh snapshot. This is what Oracle and PostgreSQL default to. InnoDB in this mode takes far fewer locks: no gap locks for ordinary index scans, and UPDATE ... WHERE releases locks on rows that did not match. It requires binlog_format = ROW (or MIXED), which is the default anyway on 5.7 and 8.0.

REPEATABLE READ: snapshot plus gap locks

-- B
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 2;     -- 250
-- A
UPDATE accounts SET balance = 300 WHERE id = 2;
-- B
SELECT balance FROM accounts WHERE id = 2;     -- still 250
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;  -- 300: locking reads see the latest committed version
COMMIT;

Plain SELECTs read from the snapshot taken at the first read of the transaction; FOR UPDATE, LOCK IN SHARE MODE, UPDATE and DELETE always operate on the current data. That mix surprises people: a transaction can read 250, then update based on a row that now says 300.

The second InnoDB-specific behavior is gap locking. It prevents phantom rows and makes statement-based replication safe, but it locks ranges that may be much wider than you expect:

-- B
START TRANSACTION;
SELECT * FROM accounts WHERE balance BETWEEN 200 AND 400 FOR UPDATE;
-- A
INSERT INTO accounts VALUES (4, 350);   -- blocks until B commits
INSERT INTO accounts VALUES (5, 50);    -- succeeds immediately

B locked the index range 200–400 and the gap up to the next entry (1000), so any insert into that range waits. Without an index on balance the whole table would effectively be locked for inserts.

SERIALIZABLE: everything is a locking read

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM accounts WHERE id = 3;    -- silently becomes ... LOCK IN SHARE MODE

Every plain SELECT takes shared locks, so writers block behind readers. Correct, slow, and rarely necessary with InnoDB's MVCC.

Looking at the locks

Watch the locks while the gap-lock transaction above is still open. On 8.0:

SELECT engine_transaction_id, object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks;

On 5.7 the equivalent is information_schema.INNODB_LOCKS together with INNODB_LOCK_WAITS, which only show locks that are actually being waited for. SHOW ENGINE INNODB STATUS\G lists lock waits and, under LATEST DETECTED DEADLOCK, the statements involved in the last deadlock – the fastest way to learn which of your queries take gap locks.

Notes

• I move most OLTP applications to READ COMMITTED: fewer gap locks, fewer deadlocks, shorter waits. The price is that a transaction may see different data on repeated reads, which most application code never relies on.
• ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction is not a bug in InnoDB; it is the expected outcome of two transactions locking rows in different orders. Retry the transaction and, if it is frequent, lock rows in a consistent order or move to READ COMMITTED.
• ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction after 50 seconds (innodb_lock_wait_timeout) usually means an application opened a transaction and forgot to commit. SELECT * FROM information_schema.INNODB_TRX WHERE trx_started < NOW() - INTERVAL 60 SECOND finds it.
• ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging is what you get combining READ COMMITTED with statement-based binlogs; switch to ROW.

Run the scenarios once on a test server. The next deadlock report will make a lot more sense.