Understanding InnoDB Repeatable Read isolation level snapshots

Question

I have the following table:

CREATE TABLE `accounts` (
  `name` varchar(50) NOT NULL,
  `balance` int NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

And it has two accounts in it. "Bob" has a balance of 100. "Jim" has a balance of 200.

I run this query to transfer 50 from Jim to Bob:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;

SELECT * FROM accounts;

SELECT SLEEP(10);

SET @bobBalance = (SELECT balance FROM accounts WHERE name = 'bob' FOR UPDATE);
SET @jimBalance = (SELECT balance FROM accounts WHERE name = 'jim' FOR UPDATE);
UPDATE accounts SET balance = @bobBalance + 50 WHERE name = 'bob';
UPDATE accounts SET balance = @jimBalance - 50 WHERE name = 'jim';

COMMIT;

While that query is sleeping, I run the following query in a different session to set Jim's balance to 500:

UPDATE accounts SET balance = 500 WHERE name = 'jim';

What I thought would happen is that this would cause a bug. The transaction would set Jim's balance to 150, because the first read in the transaction (before the SLEEP) would establish a snapshot in which Jim's balance is 200, and that snapshot would be used in the later query to get Jim's balance. So we would subtract 50 from 200 even though Jim's balance has actually been changed to 500 by the other query.

But that's not what happens. Actually, the end result is correct. Bob has 150 and Jim has 450. But I don't understand why this is.

The MySQL documentation says about Repeatable Read:

This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 15.7.2.3, “Consistent Nonlocking Reads”.

So what am I missing here? Why does it seem like the SELECT statements in the transaction are not all using a snapshot established by the first SELECT statement?

I'm not sure if two selects with different where clauses would result in reading the same snapshot! For us, humans, it is easy to decide in this particular example that the first select contains all records the 2nd and 3rd selects do. But it is not so easy to do this in an automated manner! — Shadow, Oct 12 '20 at 21:40

score 2 · Accepted Answer · answered Oct 12 '20 at 23:25

The repeatable-read behavior only works for non-locking SELECT queries. It reads from the snapshot established by the first query in the transaction.

But any locking SELECT query reads the latest committed version of the row, as if you had started your transaction in READ-COMMITTED isolation level.

A SELECT is implicitly a locking read if it's involved in any kind of SQL statement that modifies data.

For example:

INSERT INTO table2 SELECT * FROM table1 WHERE ...;

The above locks examined rows in table1, even though the statement is just copying them to table2.

SET @myvar = (SELECT ... FROM table1 WHERE ...);

This is also copying a value from table1, into a variable. It locks the examined row in table1.

Likewise SELECT statements that are invoked in a trigger, or as part of a multi-table UPDATE or DELETE, and so on. Anytime the SELECT is part of a larger statement that modifies any data (in a table or in a variable), it locks the rows examined by the SELECT.

And therefore it's a locking read, and behaves like an UPDATE with respect to which row version it reads.

Understanding InnoDB Repeatable Read isolation level snapshots

1 Answers1