Remember a MySQL environment RR isolation level converted to RC problem

First understand the difference between RR (Repeatable-read) and RC (read-committed).

The RR isolation level increases the gap lock, avoids phantom reads, and prevents non-repeatable reads so that queries and modifications within the same transaction are consistent. The default isolation level for MySQL is RR.

Although the RC isolation level in the same transaction will exist to query the phenomenon of different data, but this data must be submitted, is the real data stored in the hard disk. So don't worry too much, and the RC isolation level lowers the lock granularity, and it's not useless. Oracle and SQL The default isolation level for the server is similar to RC.

So that is not to say that RC is absolutely bad, to see the scene to choose, and here is just a brief introduction, do not intend to go deep.

Operating Procedure Description: Because of high system concurrency, there are multiple sessions that may update the same record at the same time, but the values are the same. The problem is that there is a problem in the transaction where the RR isolation level is converted to RC, causing the data to return incorrectly, causing the code to return an error, but the data is accurate.

Normal RR transactions

First look at the current environment information:

#当前的mysql版本mysql > SELECT @ @version; +------------+| @ @version |+------------+| 5.6.39-log |+------------+1 row in Set (0.00 sec) #当前的隔离级别mysql > select @ @tx_isolation; +-----------------+| @ @tx_isolation |+-----------------+| Repeatable-read |+-----------------+1 row in Set (0.00 sec) #当前的binlog格式mysql > show variables like ' Binlog_format '; +-- -------------+-------+| variable_name | Value |+---------------+-------+| Binlog_format | MIXED |+---------------+-------+1 row in Set (0.00 sec)

Let's look at a normal transaction:

#开启事务 Mysql> Begin; Query OK, 0 rows Affected (0.00 sec)	#开启事务 Mysql> begin; Query OK, 0 rows Affected (0.00 sec)
#当前记录是一致的 Mysql> Select Express_ Cost from m_order_sub where order_sub_no = ' O152022324482662671828 '; +--------------+ | express_cost | +--------------+ |         | +--------------+ 1 row in Set (0.02 sec)	#当前记录是一致的 Mysql> select Express_cost from m_order_sub where order_sub_no = ' O152022324482662671828 '; +--------------+ | express_cost | +--------------+ |         | +--------------+ 1 row in Set (0.02 sec)
#这边先更新一条记录 mysql> Update m_order_sub Set express_cost = where order_sub_no = ' O152022324482662671828 '; Query OK, 1 row affected (0.01 sec) Rows matched:1 changed:1 warnings:0
	#这边后更新一条记录, but there is no commit on the other side, so this side is waiting to release the lock Update m_order_sub Set express_cost = where order_sub_no = ' O152022324482662671828 ';
#这边再查询一次, record successful changes Mysql> Select Express_cost from m_order_sub where order_sub_no = ' O152022324482662671828 '; +--------------+ | Express_cost | +--------------+ | 3000 | +--------------+ 1 row in Set (0.00 sec)
#提交事务 Mysql> commit; Query OK, 0 rows affected (0.01 sec)	#然后锁释放后这边的更新也执行完了, but because the updated values are the same, they are not modified to the record, changed is 0 Query OK, 0 rows affected (12.40 sec) Rows matched:1 changed:0 warnings:0
#这边再查询一次, record successful changes, 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)	#这边查询结果是旧的, because the record is not modified, it is also 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)
	#提交并退出事务 Mysql> commit; Query OK, 0 rows affected (0.13 sec)
	#这时就显示最新的数据了 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 is not modified, so the data that is displayed is also the beginning of the transaction, which guarantees the repeatable read characteristics of the RR level.

Problem phenomenon

Here is another abnormal situation, the environment is consistent with the above, there is no change, we have to look directly at the picture:

As you can see, the execution is consistent with the above, and the right side of the transaction waits 12 seconds after execution, that is, after the left commit. However, it becomes a non-repeatable read, the right side of the transaction without commit can see the latest submitted data, very strange.

Workaround

The first scenario: 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-repeatable reading. Both sides can find new data that has been submitted.

#更改mysql全局隔离级别为RCset Global tx_isolation = ' read-committed '

After the change, the global has become non-repeatable read, and there is no gap lock, and because you can see the new data has been submitted, so the above normal situation will be consistent with the following, but does not mean that there is a problem, which is in itself the characteristics of the RC isolation level.

And then someone said, it's not a problem, it's just a matter of changing the whole thing into the same thing. So there's a second option.

The second scenario: Change the Binlog format to row without changing the isolation level, the problem is really solved.

#把全局binlog格式改成ROW格式set Global Binlog_format = ' ROW ';

In the above see the original Binlog format is mixed mixed mode, now change to row mode, and then try again.

OK, everything is OK, this is the RR feature, Repeatable read.

Remember a MySQL environment RR isolation level converted to RC problem