2

I know that using the locks or MVCC in Mysql can achieve concurrency control, such as repeatable-reading. But I don't know how MVCC avoids phantom-reading. In other places, I learned that it is generally implemented through MVCC and Gap-Lock, but what I currently understand is that MVCC does not need locks, that is, both updates and deletions are implemented using undo-logs. If so, how do MVCC and the lock mechanism work together?

For example, to avoid phantom-reading, would MVCC add a gap-lock on some rows in T1? If so, how MVCC does when updates occurred in T2, just appends a update undo-log generally? or blocks it?

The Impaler
  • 45,731
  • 9
  • 39
  • 76
ruokuanwu
  • 45
  • 7

1 Answers1

6

MySQL (specifically, InnoDB) does not support REPEATABLE-READ for locking statements. For example, UPDATE, DELETE or SELECT...FOR UPDATE. These statements always take locks on the most recently committed row version, as if the transaction isolation level were READ-COMMITTED.

You can observe this happening:

mysql> create table mytable (id int primary key, x int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into mytable values (1, 42);
Query OK, 1 row affected (0.02 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mytable;
+----+------+
| id | x    |
+----+------+
|  1 |   42 |
+----+------+

So far, so good. Now open a second window and update the value:

mysql> update mytable set x = 84;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Now back in the first window, a non-locking read still views the original value because of REPEATABLE-READ, but a locking read views the most recently committed version:

mysql> select * from mytable;
+----+------+
| id | x    |
+----+------+
|  1 |   42 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from mytable for update;
+----+------+
| id | x    |
+----+------+
|  1 |   84 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from mytable;
+----+------+
| id | x    |
+----+------+
|  1 |   42 |
+----+------+
1 row in set (0.00 sec)

You can go back and forth as many times as you want, and the same transaction can return both values, depending on doing a locking read vs. non-locking read.

This is a strange behavior of InnoDB, but it allows reads to not be blocked. I have used other MVCC implementations such as InterBase/Firebird, which solve this differently. It would block the read until the transaction in the second window commits or rolls back. If it rolls back, then the locking read can read the original value. If the other transaction commits, then the locking read gets an error.

InnoDB makes a different choice on how to implement MVCC, to avoid blocking the read. But it causes the strange behavior where a locking read must view the latest committed row version.

As the song says, "you can't always get what you want."

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • First of all, thank you very much for your answer. Do you want to express that MVCC implementation of MYSQL would not block the select(for update) in RR? In fact, what I want to ask is how to use MVCC and locks to solve phantom-reading, due to general definition of MVCC, it can achieve RR by inserting the undo-logs. However, it seems that MVCC(lock-free) cannot avoid phantom reading without lock. This is exactly what I want to ask, how does MVCC and lock work together. Is there any problem with what I said? – ruokuanwu Jul 21 '21 at 15:02
  • Have you read https://dev.mysql.com/doc/refman/8.0/en/innodb-next-key-locking.html ? I believe that explains it pretty well. – Bill Karwin Jul 21 '21 at 15:06
  • Thank you very much, I just have finished reading. So what I understand is to avoid phantom-reading with next-lock, but will MVCC still be used here? If so, how do they collaborate? Or it is completely realized through the lock mechanism and no longer uses MVCC here. Forgive me for being a beginner. Thank you very much! – ruokuanwu Jul 21 '21 at 16:08
  • You understand correctly that locking is what prevents your session from getting phantom reads. MVCC is still useful because _other_ sessions can read with non-locking reads, even if your session has locked the rows. – Bill Karwin Jul 21 '21 at 16:16
  • I read some article abouts MVCC againc combined with your reply. Can I think so that MVCC is only lock-free with snapshot reads, but still needs to lock when facing the insert/update/select(for update), and an undo-log will be added after the operations? – ruokuanwu Jul 21 '21 at 16:46
  • Yes, the undo log is a copy of the original data, so it can be restored if you don't commit an UPDATE or DELETE. Also so the data can be read by RR transactions that need to keep viewing it for their snapshot. But again, this is without locking. Locking has little to do with snapshots, it's more to govern concurrent sessions that want to update rows. – Bill Karwin Jul 21 '21 at 17:31