Transactions in InnoDB: REPEATABLE READ, implicit commits and gap locks

The SQL course defined transactions and isolation anomalies; this lesson is how InnoDB implements them. Each run here gets a single connection, so behaviour that needs two concurrent sessions (snapshots, gap locks, deadlocks) is described, not run: the three sections on reads, locks and deadlocks are definitions, and only the examples are observed output.

The defaults

1CREATE TABLE jobs (id INT PRIMARY KEY, status VARCHAR(10) NOT NULL);2INSERT INTO jobs VALUES (1, 'done'), (2, 'queued'), (3, 'queued');

1START TRANSACTION;2SELECT id INTO @claimed3FROM jobs4WHERE status = 'queued'5ORDER BY id6LIMIT 17FOR UPDATE SKIP LOCKED;8COMMIT;9 10SELECT @@autocommit               AS autocommit,11       @@transaction_isolation    AS isolation,12       @@innodb_lock_wait_timeout AS lock_wait_timeout_s,13       @@innodb_deadlock_detect   AS deadlock_detect,14       @claimed                   AS claimed_job;

• autocommit is on. Every statement outside START TRANSACTION is its own transaction and commits immediately.
• The isolation level is REPEATABLE READ (PostgreSQL and SQL Server default to READ COMMITTED). Change it per session with SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED.
• A lock wait gives up after innodb_lock_wait_timeout seconds (50), and deadlock detection (innodb_deadlock_detect) is on.

The example also ran FOR UPDATE SKIP LOCKED, the job-queue pattern in which each worker takes the first row nobody else has locked.

Two kinds of read

Under REPEATABLE READ, a plain SELECT is a consistent read: it reads from a snapshot taken at the transaction's first read, using undo records (MVCC), and takes no locks. Other sessions' later commits are invisible to it until your transaction ends; your own changes are visible.

A locking read (SELECT ... FOR UPDATE, FOR SHARE), UPDATE and DELETE read the latest committed row and lock it, so within one transaction they can see a value the plain did not. Read a value you are about to change with .

1CREATE TABLE log (id INT PRIMARY KEY, note VARCHAR(40));

1START TRANSACTION;2INSERT INTO log VALUES (1, 'before CREATE TABLE');3CREATE TABLE scratch (x INT);          -- DDL commits the open transaction4ROLLBACK;5 6START TRANSACTION;7INSERT INTO log VALUES (2, 'before a second START TRANSACTION');8START TRANSACTION;                     -- commits the first one9ROLLBACK;10 11START TRANSACTION;12INSERT INTO log VALUES (3, 'plain rollback');13ROLLBACK;14 15START TRANSACTION;16INSERT INTO log VALUES (4, 'before the savepoint');17SAVEPOINT sp;18INSERT INTO log VALUES (5, 'after the savepoint');19ROLLBACK TO SAVEPOINT sp;20COMMIT;21 22SELECT id, note FROM log ORDER BY id;

Rows 1 and 2 survived a ROLLBACK because something committed them before it ran. Row 5 shows SAVEPOINT: rolling back to it undoes only what came after.

An error does not roll back the transaction

When a statement inside a transaction fails (a constraint, a bad value, a lock wait timeout), InnoDB undoes that statement, not the transaction; a deadlock is the exception described above. If the code carries on and commits, whatever succeeded before the error is committed.

1CREATE TABLE accounts (2  id      INT PRIMARY KEY,3  owner   VARCHAR(10) NOT NULL,4  balance INT NOT NULL,5  CHECK (balance >= 0)6);7INSERT INTO accounts VALUES (1, 'Nino', 300), (2, 'Ana', 50), (3, 'Luka', 300), (4, 'Eka', 50);8-- Ignores errors and commits whatever happened.9CREATE PROCEDURE careless_transfer(IN p_from INT, IN p_to INT, IN p_amount INT)10BEGIN11  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;12  START TRANSACTION;13  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;14  UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;15  COMMIT;16END;17-- Rolls the whole transaction back on any error.18CREATE PROCEDURE careful_transfer(IN p_from INT, IN p_to INT, IN p_amount INT)19BEGIN20  DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;21  START TRANSACTION;22  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;23  UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;24  COMMIT;25END;

1CALL careless_transfer(1, 2, 1000);2CALL careful_transfer(3, 4, 1000);3 4SELECT id, owner, balance FROM accounts ORDER BY id;

The careless procedure credited Ana, failed to debit Nino because of the CHECK constraint, ignored the error and committed: 1,000 created from nothing. The careful one has an EXIT HANDLER that rolls back, so nothing changed.

Common mistakes

• Running DDL inside a transaction and expecting ROLLBACK to undo the rows before it. The DDL already committed them.
• Committing after a failed statement. InnoDB rolled back only the failing statement; roll back the transaction yourself.
• Reading with a plain SELECT, then updating based on it under concurrency. Use SELECT ... FOR UPDATE or a single conditional UPDATE.
• Treating error 1213 as fatal. It means "your transaction was rolled back, try again".

What interviewers ask

• What is InnoDB's default isolation level, and does it prevent phantoms? REPEATABLE READ; snapshots hide new rows from plain reads, and next-key locks block inserts into ranges read with locking reads.
• What is a gap lock? A lock on the space between index entries that prevents inserts there; part of a next-key lock.
• Does an error inside a transaction roll it back? Only the failing statement; the application must issue ROLLBACK.