[REPEATABLE READ]

First set database isolation level to REPEATABLE READ:

set global transaction isolation level REPEATABLE READ ;
set session transaction isolation level REPEATABLE READ ; 
Copy the code

[REPEATABLE READ] can solve one of the problems

[REPEATABLE READ] Isolation level solves the problem of unrepeatable READ. Multiple reads in a transaction will not have different results, ensuring REPEATABLE READ. Again, the example from the previous article mimicking unrepeatable reads: transaction 1:

START TRANSACTION; 1.SELECT sleep(5); 2.UPDATE users SET state=1 WHERE id=1;
COMMIT;
Copy the code

Transaction 2:

START TRANSACTION; 1.SELECT * FROM users WHERE id=1; 2.SELECT sleep(10); 3.SELECT * FROM users WHERE id=1;
COMMIT;  
Copy the code

Transaction 1 executes before transaction 2. Transaction 1 execution information:

[SQL 1]START TRANSACTION; Affected row: 0 time: 0.000s [SQL]SELECT sleep(5); Affected row: 0 time: 5.001s [SQL 3]UPDATE users SET state=1 WHERE id=1; Affected rows: 1 time: 0.000s [SQL 4]COMMIT; Affected row: 0 Time: 0.062sCopy the code

Transaction 2 execution information:

[SQL 1]
    SELECT * FROM users WHERE id=1; Affected row: 0 time: 0.000s [SQL]SELECT sleep(10); SQL > select * from 'SQL' where 'SQL' = '0'SELECT * FROM users WHERE id=1; Affected row: 0 time: 0.001s [SQL 4]COMMIT; Affected rows: 0 Time: 0.001sCopy the code

Execution Result:

conclusion

REPEATABLE READ isolation level can solve the root cause of REPEATABLE READ because [READ COMMITTED] is not the same as READ View. [READ COMMITTED] : All data COMMITTED before the current statement is COMMITTED is visible. [REPEATABLE READ] : all data committed before the current transaction is visible. With the isolation level of [REPEATABLE READ], the current Global READ View is generated when the transaction is created and is maintained until the end of the transaction. This enables repeatable reads.

In simulating a non-repeatable read transaction, a Read View is generated when transaction 2 is created. The transaction ID of transaction 1 trx_ID1 =1 and the transaction ID of transaction 2 trx_ID2 =2. Assume transaction trx_id=0 for the row before transaction 2 reads the data for the first time. Read View {1} trx_ID_min =1, trx_ID_max =1 Because trx_id(0)<trx_id_min(1), the current value of the row is visible, return state=0 for that visible row. Because at the [REPEATABLE READ] isolation level, the READ View is regenerated only when the transaction is created. Transaction 1 updates the data before transaction 2 reads the data for the second time. Transaction trx_id=1. Trx_id_min (1)=trx_id(1)=trx_id_max(1), this row is not visible to transaction 2. Fetch the latest undo-log version number from the rollback segment indicated by the DB_ROLL_PTR pointer on this row and return the value of state=0 for this visible row. Therefore, transaction 2 reads the data for the second time in the same way as it does for the first time, with state=0. The data is repeatable.

From [SQL 3] in transaction 1’s execution information, we can know that the [REPEATABLE READ] isolation level READ operation is also unlocked. This is because the s-lock is released at the end of the transaction if the read requires it. Transaction 1[SQL 3] will wait for transaction 2’s lock to be released. The reality is not.

As we know, the InnoDB engine of MySql realizes non-blocking reading while ensuring data security through MVCC. MVCC mode requires extra storage and more line checking; However, it guarantees read operation without locking and improves performance, which is a typical realization of sacrificing space for time. Note that MVCC only works at [READ COMMITTED] and [REPEATABLE READ] isolation levels. The other two isolation levels are incompatible with MVCC because [READ UNCOMMITTED] always reads the latest row, not the row that matches the current version of the transaction. [SERIALIZABLE] locks all rows read.

By personally practicing and analyzing the working mechanism of the two isolation levels [READ COMMITTED] and [REPEATABLE READ], we can also deeply understand that the way each database engine implements each isolation level is not one by one corresponding to the blocking protocol definition in standard SQL.

REPEATABLE READ [REPEATABLE READ] can solve the second problem

Phantom reading is actually a special case of unrepeatable reading. Non-repeatable reads are generated when data is modified or updated. Phantoms are created by inserting or deleting data. There are two cases of phantom reading: one thing reads a record before, and then finds that the record is missing and deleted by other transactions; the other thing is that the record does not exist when it is read before, and then finds that there is this record again, and another thing inserts a record.

Transaction 1:

START TRANSACTION;
SELECT * FROM users;
SELECT sleep(10);
SELECT * FROM users;
COMMIT;
Copy the code

Transaction 2:

START TRANSACTION;
SELECT sleep(5);
INSERT INTO users VALUES(2.'song'.2);
COMMIT;
Copy the code

Execution Result:

1. Expected results

2. Actual results

Transaction 1 does not read the newly inserted data of transaction 2, and there is no phantom reading. REPEATABLE READ isolation level can solve phantom READ problem. According to the definition of lockdown protocol, the three-level lockdown protocol cannot solve the problem of illusory reading. Only the strongest blocking protocol, which locks both read and write to the entire table, can solve the phantom read problem. But doing so would serialize all operations, and the ability of the database to support concurrency would become extremely poor. So Mysql’s InnoDB engine solved the phantom problem by its own way in the [REPEATABLE READ] isolation level. Let’s explore how InnoDB engine solved phantom problem.

InnoDB has three kinds of row Lock algorithms: 1.Record Lock: a Lock on a single row Record. 2.Gap Lock: A Gap Lock that locks a range but excludes the record itself. The purpose of the GAP lock is to prevent the illusion of two current reads of the same transaction. Next-key Lock: 1+2, locks a range and locks the record itself. The main purpose is to solve the problem of illusory reading.

In REPEATABLE READ level, if the query condition can use the upper unique index or a unique query condition, then only add the row lock (with the unique query condition, of course there is no phantom READ phenomenon); If it is a range query, a Gap lock or next-key lock (row lock +Gap lock) is added to the range. In theory, hallucinations do not occur.

Verify the existence of Gap and next-key locks

We can verify the existence of Gap Lock and next-key Lock by ourselves. First we need to index the state field. Then prepare several pieces of data, as shown below:

Transaction 1

START TRANSACTION;  
① SELECT * FROM users WHERE state=3 for UPDATE;
Copy the code

Transaction 2:

[SQL]INSERT INTO users VALUES(5,'song',1); [Err] 1205 - Lock wait timeout exceeded; try restarting transaction [SQL]INSERT INTO users VALUES(6,'song',2); [Err] 1205 - Lock wait timeout exceeded; try restarting transaction [SQL]INSERT INTO users VALUES(6,'song',3); [Err] 1205 - Lock wait timeout exceeded; try restarting transaction [SQL]INSERT INTO users VALUES(6,'song',4); [Err] 1205 - Lock wait timeout exceeded; try restarting transaction [SQL]INSERT INTO users VALUES(5,'song',0); [SQL]INSERT INTO users VALUES(6,'song',5); [SQL]INSERT INTO users VALUES(7,'song',7); Affected row: 1 Time: 0.041sCopy the code

Because InnoDB uses next-key locks for rows, it does not Lock a single value, but a range (GAP). The above index values have 1, 3,5,8, and the recorded GAP interval is as follows: (-∞,1], (1,3], (3,5], (5,8], (8,+∞). It’s a space that opens on the left and closes on the right. Note that the InnoDB storage engine also applies a Gap Lock to the next key in the secondary index. Mysql > lock (1,3); insert (1,3); insert (3,5); Inserting values outside this range is fine.

REPEATABLE READ [REPEATABLE READ] Whether to lock or not?

Another question arose when I understood how the [REPEATABLE READ] isolation level solved the phantom problem. [READ COMMITED] and [REPEATABLE READ] use MVCC to avoid Lock problem, but [REPEATABLE READ] also uses Gap Lock or next-key Lock to solve phantom READ problem. REPEATABLE READ [REPEATABLE READ]; I was puzzled over this question until I read this article and understood it a little bit.

What’s the difference between InnoDB and SERIALIZABLE if InnoDB locks normal queries? InnoDB provides next-key Lock, but requires the application to Lock itself. Again, consistent reads (snapshot reads) and current reads are involved. If we choose consistent Read, which is the MVCC mode, the Read does not need to be locked, and the Read data is controlled by the Read View. If we select the current read, the read needs to be locked, that is, next-key Lock. Other write operations need to wait for the next-key Lock release before writing. In this way, data is read in real time.

Consistent reads are easy to understand, read without locking, read without blocking. Read – to – read locking may be difficult to understand, we can use an example to understand:

TRANSACTION 1 TRANSACTION 2 START TRANSACTION; START TRANSACTION; SELECT * FROM users; INSERT INTO users VALUES (2, 'swj',2); COMMIT; SELECT * FROM users; SELECT * FROM users LOCK IN SHARE MODE; SELECT * FROM users FOR UPDATE;Copy the code

Execution Result:

mysql> SELECT * FROM users;
+----+------+-------+
| id | name | state |
+----+------+-------+
|  1 | swj  |     1 |
+----+------+-------+
1 row in set (0.04 sec)

mysql> SELECT * FROM users;
+----+------+-------+
| id | name | state |
+----+------+-------+
|  1 | swj  |     1 |
+----+------+-------+
1 row in set (0.08 sec)

mysql> SELECT * FROM users LOCK IN SHARE MODE;
+----+------+-------+
| id | name | state |
+----+------+-------+
|  1 | swj  |     1 |
|  2 | swj  |     2 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM users FOR UPDATE;
+----+------+-------+
| id | name | state |
+----+------+-------+
|  1 | swj  |     1 |
|  2 | swj  |     2 |
+----+------+-------+
2 rows in set (0.00 sec)
Copy the code

Conclusion: MVCC implements snapshot read, next-key Lock implements current read. MySQL InnoDB’s repeatable read does not guarantee the avoidance of phantom read, but requires the application to use Lock read to ensure, and this Lock read mechanism is Next Key Lock.

Akiko: That kind of thing

Disclaimer: This article is a summary of the blogger’s learning experience, the level is limited, if improper, welcome correction. If you think it’s good, you might as well take a look at mine
【 wechat official account 】To get updates as soon as possible. Please indicate the source of reprint and quotation.