“This is the 10th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

1. How InnoDB achieves phantom avoidance in RR isolation level?

InnoDB engine, RR isolation level, can not completely prevent phantom, to avoid phantom, make sure gap lock is enabled, i.e. innodb_locks_unsafe_for_binlog parameter value is off (default value is off).

  1. Representation: Snapshot reads are implemented through MMVC (transaction versioning based protocol) and undo logging.

Snapshot read: common select select * from table Current read: add, delete, modify, query with lock, read the latest data select * from table where? lock in share mode;

select * from table where ? for update; insert; update ; delete;

For snapshot read scenarios, MVCC version management is used to solve the phantom read problem. That is, transaction A only performs two query operations, and the result of the second query is the original data information even if there is a qualified insert in the middle of the two queries. But that’s just a symptom. Let’s move on to the underlying cause.

  1. Internal cause: next-key (row lock and gap lock)

For the current read situation, mysql is handled by the next-key lock. In the case of the current read, a range lock is added to hold an interval within which other transactions will wait for the current transaction to commit if they insert.

  • In the case of all where conditions hit, no gap lock

Since the operation is within this precise range, the data will not be affected by the addition of new data, so no phantom reading will occur, nor will the gap lock be triggered

Select * from table where id = 1, 3, 5
select * from table where id in,3,5 (1)Copy the code
  • Trigger a GAP lock if not all where conditions are hit
Select * from table where id = 1, 3, 5
Insert (id = 3, id = 5); insert (id = 5, id = 5)
select * from table where id in,3,5 (1)Copy the code
  • Gap lock range

If the id value is 2,6,9,11,15, then the range of (- up, 2), (2, 6], (6, 9], (9, 11), (11, 15], (15, + up)