Conversion of RR isolation level to RC isolation level in MySQL

Conversion of RR isolation level to RC isolation level in MySQL

First understand the difference between RR (REPEATABLE-READ) and RC (READ-COMMITTED.

The RR isolation level adds a gap lock, which avoids Phantom reads and prevents repeated reads, so that the queries and modifications in the same transaction are consistent. The default isolation level of mysql is RR.

Although different data may be queried in the same transaction at the RC isolation level, these data must have been committed and actually stored in the hard disk. So don't worry too much, and the RC isolation level reduces the lock granularity, which is not useless. The default isolation levels of Oracle and SQL server are similar to those of RC.

So it doesn't mean that RC is definitely not good. It depends on the scenario to choose from. Here it is just an introduction and it is not intended to be further explored.

Operation Process description: due to the high concurrency of the system, multiple sessions may update the same record at the same time, but the values are the same. The problem is that the RR isolation level in the transaction is converted to RC, which leads to incorrect data return and code return errors, but the data is accurate.

Normal RR transactions

First view the current environment information:

# Current mysql version
Mysql> select @ version;
+ ------------ +
| @ Version |
+ ------------ +
| 5.6.39-log |
+ ------------ +
1 row in set (0.00 sec)
# Current isolation level
Mysql> select @ tx_isolation;
+ ----------------- +
| @ Tx_isolation |
+ ----------------- +
| REPEATABLE-READ |
+ ----------------- +
1 row in set (0.00 sec)
# Current binlog format
Mysql> show variables like 'binlog _ format ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Binlog_format | MIXED |
+ --------------- + ------- +
1 row in set (0.00 sec)

First look at a normal transaction:

# Start a transaction Mysql> begin; Query OK, 0 rows affected (0.00 sec)	# Start a transaction Mysql> begin; Query OK, 0 rows affected (0.00 sec)
# The current record is consistent Mysql> select express_cost from m_order_sub where order_sub_no = 'o152022324482662671828 '; + -------------- + | Express_cost | + -------------- + | 1, 2000 | + -------------- + 1 row in set (0.02 sec)	# The current record is consistent Mysql> select express_cost from m_order_sub where order_sub_no = 'o152022324482662671828 '; + -------------- + | Express_cost | + -------------- + | 1, 2000 | + -------------- + 1 row in set (0.02 sec)
# Update a record first Mysql> update m_order_sub set express_cost = 3000 where order_sub_no = 'o152022324482662671828 '; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
	# Update a record later, but there is no commit on the other, so the lock is waiting to be released. Update m_order_sub set express_cost = 3000 where order_sub_no = 'o152022324482662671828 ';
# The record is successfully modified after another query. Mysql> select express_cost from m_order_sub where order_sub_no = 'o152022324482662671828 '; + -------------- + | Express_cost | + -------------- + | 1, 3000 | + -------------- + 1 row in set (0.00 sec)
# Commit transactions Mysql> commit; Query OK, 0 rows affected (0.01 sec)	# After the lock is released, the update is also completed, but because the update value is the same, it is not modified to the record and Changed to 0. Query OK, 0 rows affected (12.40 sec) Rows matched: 1 Changed: 0 Warnings: 0
# Here we will query again, and the record is successfully modified, which is the latest data Mysql> select express_cost from m_order_sub where order_sub_no = 'o152022324482662671828 '; + -------------- + | Express_cost | + -------------- + | 1, 3000 | + -------------- + 1 row in set (0.00 sec)	# The query result here is old. Because the record is not modified, the data displayed at the beginning of the transaction is Mysql> select express_cost from m_order_sub where order_sub_no = 'o152022324482662671828 '; + -------------- + | Express_cost | + -------------- + | 1, 2000 | + -------------- + 1 row in set (0.00 sec)
	# Commit and exit the transaction Mysql> commit; Query OK, 0 rows affected (0.13 sec)
	# The latest data is displayed. Mysql> select express_cost from m_order_sub where order_sub_no = 'o152022324482662671828 '; + -------------- + | Express_cost | + -------------- + | 1, 3000 | + -------------- + 1 row in set (0.00 sec)

This is a normal situation. Because the record is not modified, the data at the beginning of the transaction is displayed, ensuring repeatable read at the RR level.

Symptom

Next, let's look at another abnormal situation. The environment is the same as above and has not changed. Let's look at the figure directly:

We can see that the execution method is the same as above. The transaction on the right is executed after 12 seconds, that is, after the commit on the left. However, the transaction on the right can see the latest committed data without a commit.

Solution

Solution 1: Changing the isolation level to RC seems to solve the problem, but it solves the problem on the left and changes the Repeatable read feature to non-repeated read. In this way, both sides can find the submitted new data.

# Change the mysql global isolation level to RC

Set global tx_isolation = 'read-committed'

After the change, the global data becomes non-repeatable, and no gap lock is available. because we can see the new data already submitted, the above normal conditions will be consistent with the following, but it does not mean that there is a problem. This is a feature of the RC isolation level.

Some people said that this was not a solution, but it was just to change all the problems to the same one, as if they were. So there is a second solution.

Solution 2: Change the binlog format to ROW without changing the isolation level. The problem is solved.

# Change the global binlog format to the ROW format

Set global binlog_format = 'row ';

As shown above, the original binlog format is MIXED mode. Change it to ROW mode and try again.

Okay. Everything is normal. This is the RR feature and can be re-read.

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151339.htm