REPEATABLE READ and consistent read in InnoDB and locking
Hi Folks,
I've been reading the MySQL Docs on InnoDB's transaction model and the concept of "Consistent Read" and have some questions/queries/concerns.
Essentially, from the textbooks, my understanding of the isolation level REPEATABLE READ is that uses READ/WRITE locks on access/change, identical to SERIALIZABLE except that range locks aren't used, as an isolation-versus-concurrency trade-off. Fine.
However, I think that MySQL's default "consistent read" implementation using MVCC for SELECT queries means things aren't working as I'd expected between transactions that I'd otherwise expect to be isolated in RR.
Here's an example:
Database set-up:
CREATE TABLE names (
id int unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
count int unsigned NOT NULL default '0',
PRIMARY KEY(id)
) ENGINE=InnoDB;
INSERT INTO names VALUES (NULL, "alice", 1);
Now the transactional code itself (T1 & T2 being the two transactions in play, both operating in REPEATABLE READ):
T1> BEGIN;
T1> SELECT * FROM names WHERE id = 1;
+----+---------+-------+
| id | name | count |
+----+---------+-------+
| 1 | alice | 1 |
+----+---------+-------+
T2> BEGIN;
T2> SELECT * FROM names WHERE id = 1;
+----+---------+-------+
| id | name | count |
+----+---------+-------+
| 1 | alice | 1 |
+----+---------+-------+
T2> UPDATE names SET count = count + 1 WHERE id = 1;
T2> SELECT * FROM names WHERE id = 1;
+----+---------+-------+
| id | name | count |
+----+---------+-------+
| 1 | alice | 2 |
+----+---------+-------+
T2> COMMIT;
T1> SELECT * FROM names WHERE id = 1;
+----+---------+-------+
| id | name | count |
+----+---------+-------+
| 1 | alice | 1 |
+----+---------+-------+
T1> UPDATE names SET count = count + 1 WHERE ID = 1;
T1> SELECT * FROM names WHERE id = 1;
+----+---------+-------+
| id | name | count |
+----+---------+-------+
| 1 | alice | 3 | << Lions and Tigers and Bears, OH MY
+----+---------+-------+
T1> COMMIT;
So the problem, I think is that T1's last SELECT is seeing the result of T2's update as well as its own despite this being incorrect (I think based on my textbooks) for what I'd expect for REPEATABLE READ.
This issue is that as far as I can tell, with "consistent read" MVCC, no read locks are acquired for SELECT, contrary to what I'd normally expect to happen with RR, where read locks should be acquired on SELECT.
This appears to be confirmed by the MySQL's InnoDB docs.
Whacking up the isolation level to SERIALIZABLE, as noted in the docs, makes this work as I'd hoped, with SELECT taking out the required locks, and a deadlock and/or waits for locks occurring under various combinations of the above statements across the transactions, as I'd expect, and since no range locks are in use, this surely is how things should operate in RR too?!
Is this a design decision on MySQL's part, or am I misinterpreting the situation?
To 'fix' SELECT under RR, it seems I need to use "SELECT... LOCK IN SHARE MODE" and "SELECT... FOR UPDATE" to ensure read and/or write locks are taken out on rows my transactions work with.
This is fine, and I'm happy to alter my code as necessary, I was just slightly surprised and wanted to confirm my investigations.
Thoughts?
Edited 1 time(s). Last edit at 08/13/2008 09:55AM by Ian Maddison.