Shulou(Shulou.com)06/01 Report--

Let me tell you a little bit about how the RR isolation level in the mysql environment is converted to RC. Have you ever heard of similar topics before? If you are interested, let's take a look at this article. I believe it will be of some help to you after reading how the RR isolation level of the mysql environment is converted to RC.

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

The RR isolation level adds gap locks, avoids phantom reads, and prevents unrepeatable reads so that queries and modifications in the same transaction are consistent. The default isolation level for MySQL is RR.

Although the RC isolation level may query different data in the same transaction, the data must be submitted and are actually stored on the hard disk. So don't worry too much, and the RC isolation level reduces lock granularity, which is not useless. Oracle and sql server default isolation levels are similar to RC.

So this is not to say that RC is absolutely bad, it depends on the scene to choose, and here is only a brief introduction, not going into depth.

Operation flow description: due to the high concurrency of the system, there is a problem that multiple sessions may update the same record at the same time, but the value is the same. The problem is that there is a problem that the RR isolation level is converted to RC in the transaction, which results in incorrect data return and code return error, but the data is accurate.

Normal RR transaction

First, take a look at the current environmental 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)

Let's 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 |

+-+

| | 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 |

+-+

| | 2000 |

+-+

1 row in set (0.02 sec)

# update a record here first

Mysql > update m_order_sub set express_cost = 3000 where order_sub_no = 'O152022324482662671828'

Query OK, 1 row affected (0.01sec)

Rows matched: 1 Changed: 1 Warnings: 0

# update a record on this side, but there is no commit on the other side, so this side is waiting for the lock to be released

Update m_order_sub set express_cost = 3000 where order_sub_no = 'O152022324482662671828'

# query here again, and the record has been modified successfully

Mysql > select express_cost from m_order_sub where order_sub_no = 'O152022324482662671828'

+-+

| | express_cost |

+-+

| | 3000 |

+-+

1 row in set (0.00 sec)

# commit transaction

Mysql > commit

Query OK, 0 rows affected (0.01 sec)

# then after the lock is released, the update on this side is also completed, but because the value of the update is the same, it is not modified to the record, Changed is 0

Query OK, 0 rows affected (12.40 sec)

Rows matched: 1 Changed: 0 Warnings: 0

# query here again. The record has been modified successfully, and it is the latest data.

Mysql > select express_cost from m_order_sub where order_sub_no = 'O152022324482662671828'

+-+

| | express_cost |

+-+

| | 3000 |

+-+

1 row in set (0.00 sec)

# the query result here is old, because the record has not been modified, so it also shows the data at the beginning of the transaction.

Mysql > select express_cost from m_order_sub where order_sub_no = 'O152022324482662671828'

+-+

| | express_cost |

+-+

| | 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 will be displayed at this time

Mysql > select express_cost from m_order_sub where order_sub_no = 'O152022324482662671828'

+-+

| | express_cost |

+-+

| | 3000 |

+-+

1 row in set (0.00 sec)

This is a normal situation, because the record has not been modified, so it also displays the data at the beginning of the transaction, ensuring the repeatability of the RR level.

Problem phenomenon

Let's take a look at another abnormal situation. The environment is the same as above and has not changed. Let's look at the picture directly.

As you can see, the execution mode is the same as above. The transaction on the right waits for 12 seconds before it executes, that is, after the commit on the left. However, it becomes unrepeatable, and it is weird to see the latest submitted data without commit in the transaction on the right.

Solution method

The first solution: changing the isolation level to RC seems to solve the problem, but it solves the problem on the left, changing the repeatable feature to non-repeatable. In this way, new data that has been submitted can be found on both sides.

# change the global isolation level of mysql to RCset global tx_isolation = 'READ-COMMITTED'

After the change, the overall situation becomes unrepeatable, and there is no gap lock, and it is precisely because you can see the new data that has been submitted, so the normal situation above will be the same as below, but it does not mean that there is a problem, which is the characteristic of RC isolation level.

Then someone said, it didn't solve the problem, it just changed it all to the same thing, as if so. So there is a second plan.

The second solution: change the binlog format to ROW without changing the isolation level, the problem is really solved.

# change the global binlog format to ROW format set global binlog_format = 'ROW'

See above that the original binlog format is MIXED mixed mode, now change to ROW mode, try again.

All right, everything is fine. This is the characteristic of RR, which can be read repeatedly.

What do you think of this article on how to convert RR isolation levels into RC in mysql environments? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.