MySQL中隔离级别RC与RR的区别及说明

吾爱主题阅读：154 2024-04-01 23:21:25 评论：0

MySQL隔离级别RC与RR的区别

RR 支持 gap lock(next-key lock)，而RC则没有gap lock。因为MySQL的RR需要gap lock来解决幻读问题。而RC隔离级别则是允许存在不可重复读和幻读的。所以RC的并发一般要好于RR；

RC 隔离级别，通过 where 条件走非索引列过滤之后，不符合条件的记录上的行锁，会释放掉(虽然这里破坏了“两阶段加锁原则”)；但是RR隔离级别，通过 where 条件走非索引列过滤之后，即使不符合where条件的记录，也是会加行锁。所以从锁方面来看，RC的并发应该要好于RR；可以减少一部分锁竞争，减少死锁和锁超时的概率。

RC 隔离级别不支持 statement 格式的bin log，因为该格式的复制，会导致主从数据的不一致；只能使用 mixed 或者 row 格式的bin log; 这也是为什么MySQL默认使用RR隔离级别的原因。复制时，我们最好使用：binlog_format=row

MySQL5.6 的早期版本，RC隔离级别是可以设置成使用statement格式的bin log，后期版本则会直接报错；

简单而且，RC隔离级别时，事务中的每一条select语句会读取到他自己执行时已经提交了的记录，也就是每一条select都有自己的一致性读ReadView; 而RR隔离级别时，事务中的一致性读的ReadView是以第一条select语句的运行时，作为本事务的一致性读snapshot的建立时间点的。只能读取该时间点之前已经提交的数据。

MySQL8 RC和RR隔离级别的实战

读未提交：在读未提交这个隔离级别下，即使别的事务所做的修改并未提交，也能看到其修改的数据。当事务的隔离级别处于“读未提交”时，其并发性能是最强的，但是隔离性与安全性是最差的，会出现脏读，在生产环境中不使用。

读已提交：读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。该隔离级别避免了脏读，但是却可能出现不可重复读。例如，事务A事先读取了数据，事务B紧接着更新并提交了事务，当事务A再次读取该数据时数据已经发生改变。

可重复读：是指在一个事务内多次读同一数据。假设在一个事务还没结束时，另一个事务也访问同一数据，那么在第一个事务中的两次读数据之间，即使第二个事务对数据进行了修改，第一个事务两次读到的数据也是一样的。这样在一个事务内两次读到的数据就是一样的，因此称为可重复读。读取数据的事务禁止写事务（但允许读事务），写事务则禁止任何其他事务，这样即可避免不可重复读和脏读，但是有时可能出现幻读。

序列化：提供严格的事务隔离。它要求事务序列化执行，即事务只能一个接着一个地执行，但不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也最高，性能很低，一般很少使用。在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读、还避免了幻读

一、创建测试数据

创建测试表：

1 2 3 4 5 6 create table a (id int auto_increment, a varchar (10), b varchar (10), c varchar (10), d varchar (10), primary key (id) ) engine=INNODB default charset=utf8;

插入数据

1 2 3 4 5 6 7 8 insert into a(a,b,c,d) values ( '1' , '1' , '1' , '1' ), ( '2' , '2' , '2' , '2' ), ( '3' , '3' , '3' , '3' ), ( '4' , '4' , '4' , '4' ), ( '5' , '5' , '5' , '5' ), ( '6' , '6' , '6' , '6' );

查询数据：

1 2 3 4 5 6 7 8 9 10 11 12 mysql> select * from a; + ----+------+------+------+------+ | id | a | b | c | d | + ----+------+------+------+------+ | 1 | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | 2 | | 3 | 3 | 3 | 3 | 3 | | 4 | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | 5 | | 6 | 6 | 6 | 6 | 6 | + ----+------+------+------+------+ 6 rows in set (0.00 sec)

创建索引：

1	`CREATE` `INDEX` `index_b_c` `ON` `a(b,c);`

二、RR隔离级别

1 2 3 4 5 6 7 mysql> show variables like '%iso%' ; + -----------------------+-----------------+ | Variable_name | Value | + -----------------------+-----------------+ | transaction_isolation | REPEATABLE - READ | + -----------------------+-----------------+ 1 row in set (0.00 sec)

可以从上图看出，目前MySQL处于RR的隔离级别（可重复读）

事务1：

1 2 3 4 5 mysql> begin ; Query OK, 0 rows affected (0.00 sec) mysql> update a set d= '20' where a= '1' ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

事务2：

1 2 3 4 mysql> begin ; Query OK, 0 rows affected (0.00 sec) mysql> update a set d= '20' where a= '2' ;#一直等待，最后报错 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

等待时间取决于这个参数：

1	`innodb_lock_wait_timeout`

查看mysql锁情况：

PS：因为show processlist只会显示出当前正在执行的语句，有些语句很快就执行完成了，所以有时候是看不全的，需要通过系统表查看

performance_schema.data_locks

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 mysql> select * from performance_schema.data_locks; + --------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | + --------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ | INNODB | 140166601014432:1066:140166526246928 | 14257 | 108 | 11 | test | a | NULL | NULL | NULL | 140166526246928 | TABLE | IX | GRANTED | NULL | | INNODB | 140166601014432:5:4:8:140166526244128 | 14257 | 108 | 11 | test | a | NULL | NULL | PRIMARY | 140166526244128 | RECORD | X | WAITING | 1 | | INNODB | 140166601010392:1066:140166526216544 | 14256 | 76 | 73 | test | a | NULL | NULL | NULL | 140166526216544 | TABLE | IX | GRANTED | NULL | | INNODB | 140166601010392:5:4:1:140166526213552 | 14256 | 76 | 73 | test | a | NULL | NULL | PRIMARY | 140166526213552 | RECORD | X | GRANTED | supremum pseudo-record | | INNODB | 140166601010392:5:4:3:140166526213552 | 14256 | 76 | 73 | test | a | NULL | NULL | PRIMARY | 140166526213552 | RECORD | X | GRANTED | 2 | | INNODB | 140166601010392:5:4:4:140166526213552 | 14256 | 76 | 73 | test | a | NULL | NULL | PRIMARY | 140166526213552 | RECORD | X | GRANTED | 3 | | INNODB | 140166601010392:5:4:5:140166526213552 | 14256 | 76 | 73 | test | a | NULL | NULL | PRIMARY | 140166526213552 | RECORD | X | GRANTED | 4 | | INNODB | 140166601010392:5:4:6:140166526213552 | 14256 | 76 | 73 | test | a | NULL | NULL | PRIMARY | 140166526213552 | RECORD | X | GRANTED | 5 | | INNODB | 140166601010392:5:4:7:140166526213552 | 14256 | 76 | 73 | test | a | NULL | NULL | PRIMARY | 140166526213552 | RECORD | X | GRANTED | 6 | | INNODB | 140166601010392:5:4:8:140166526213552 | 14256 | 76 | 73 | test | a | NULL | NULL | PRIMARY | 140166526213552 | RECORD | X | GRANTED | 1 | + --------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ 10 rows in set (0.00 sec)

information_schema.INNODB_TRX

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 mysql> select * from INNODB_TRX\G *************************** 1. row *************************** trx_id: 14257 trx_state: LOCK WAIT trx_started: 2022-04-23 17:09:05 trx_requested_lock_id: 140166601014432:5:4:8:140166526244128 trx_wait_started: 2022-04-23 17:09:05 trx_weight: 2 trx_mysql_thread_id: 51 trx_query: update a set d= '20' where a= '2' trx_operation_state: starting index read trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 1128 trx_rows_locked: 1 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 trx_schedule_weight: 1 *************************** 2. row *************************** trx_id: 14256 trx_state: RUNNING trx_started: 2022-04-23 17:07:20 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 3 trx_mysql_thread_id: 26 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 1128 trx_rows_locked: 7 trx_rows_modified: 1 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 trx_schedule_weight: NULL 2 rows in set (0.00 sec)

从这里可以看到，事务ID14256正在运行。

performance_schema .data_lock_waits

1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> select * from data_lock_waits\G *************************** 1. row *************************** ENGINE: INNODB REQUESTING_ENGINE_LOCK_ID: 140166601014432:5:4:8:140166526244128 REQUESTING_ENGINE_TRANSACTION_ID: 14257 REQUESTING_THREAD_ID: 108 REQUESTING_EVENT_ID: 11 REQUESTING_OBJECT_INSTANCE_BEGIN: 140166526244128 BLOCKING_ENGINE_LOCK_ID: 140166601010392:5:4:8:140166526213552 BLOCKING_ENGINE_TRANSACTION_ID: 14256 BLOCKING_THREAD_ID: 76 BLOCKING_EVENT_ID: 73 BLOCKING_OBJECT_INSTANCE_BEGIN: 140166526213552 1 row in set (0.00 sec)

从这里可以看到，因为14256事务ID阻塞了当前这个事务。

从这三张图可以看出来，第一个事务正在执行（还没commit），第二个事务的update处于锁等待状态，可能有小伙伴会说，mysql不是行级锁吗？为什么update的不是同一条数据，还是会锁全表呢？

带着这个疑问我们来看，下面来看第二个例子：

开启第一个事务：

1 2 3 4 5 mysql> begin ; Query OK, 0 rows affected (0.00 sec) mysql> update a set d= '20' where b= '2' ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

开启第二个事务：

1 2 3 4 5 mysql> begin ; Query OK, 0 rows affected (0.00 sec) mysql> update a set d= '20' where b= '3' ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

思考：为什么这个语句就可以执行成功呢？

查看mysql锁情况：

1 2 3 4 5 6 7 8 9 10 11 performance_schema.data_locks mysql> select * from performance_schema.data_locks; + --------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | + --------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-------------+ | INNODB | 140166601010392:1066:140166526216544 | 14266 | 110 | 11 | test | a | NULL | NULL | NULL | 140166526216544 | TABLE | IX | GRANTED | NULL | | INNODB | 140166601010392:5:5:3:140166526213552 | 14266 | 110 | 11 | test | a | NULL | NULL | index_b_c | 140166526213552 | RECORD | X | GRANTED | '2' , '2' , 2 | | INNODB | 140166601010392:5:4:11:140166526213896 | 14266 | 110 | 11 | test | a | NULL | NULL | PRIMARY | 140166526213896 | RECORD | X,REC_NOT_GAP | GRANTED | 2 | | INNODB | 140166601010392:5:5:4:140166526214240 | 14266 | 110 | 11 | test | a | NULL | NULL | index_b_c | 140166526214240 | RECORD | X,GAP | GRANTED | '3' , '3' , 3 | + --------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-------------+ 4 rows in set (0.00 sec)

information_schema.INNODB_TRX

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 mysql> select * from INNODB_TRX\G *************************** 1. row *************************** trx_id: 14267 trx_state: RUNNING trx_started: 2022-04-23 17:35:44 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 5 trx_mysql_thread_id: 51 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 4 trx_lock_memory_bytes: 1128 trx_rows_locked: 3 trx_rows_modified: 1 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 trx_schedule_weight: NULL *************************** 2. row *************************** trx_id: 14266 trx_state: RUNNING trx_started: 2022-04-23 17:30:50 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 5 trx_mysql_thread_id: 53 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 4 trx_lock_memory_bytes: 1128 trx_rows_locked: 3 trx_rows_modified: 1 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 trx_schedule_weight: NULL 2 rows in set (0.00 sec)

两个事务都在正常运行，互不干扰。

performance_schema.data_lock_waits

1 2	`mysql>` `select` `*` `from` `performance_schema.data_lock_waits\G` `Empty` `set` `(0.00 sec)`

锁不存在

事务1提交：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> commit ; Query OK, 0 rows affected (0.01 sec) mysql> select * from a; + ----+------+------+------+------+ | id | a | b | c | d | + ----+------+------+------+------+ | 1 | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | 20 | | 3 | 3 | 3 | 3 | 3 | | 4 | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | 5 | | 6 | 6 | 6 | 6 | 6 | + ----+------+------+------+------+ 6 rows in set (0.00 sec)

从这里可以看出，因为事务2并没有提交，所以b=3的数据并没有更新

事务2提交：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> commit ; Query OK, 0 rows affected (0.01 sec) mysql> select * from a; + ----+------+------+------+------+ | id | a | b | c | d | + ----+------+------+------+------+ | 1 | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | 20 | | 3 | 3 | 3 | 3 | 20 | | 4 | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | 5 | | 6 | 6 | 6 | 6 | 6 | + ----+------+------+------+------+ 6 rows in set (0.00 sec)

结论

当字段没有建立索引的时候，该字段作为update条件的话，会锁全表。

三、RC隔离级别

设置隔离级别为RC

1	`mysql>` `set` `global` `transaction_isolation=` `'read-committed'`

事务1：

1 2 3 4 5 mysql> begin ; Query OK, 0 rows affected (0.00 sec) mysql> update a set d= '10' where a= '1' ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

事务2：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 mysql> begin ; Query OK, 0 rows affected (0.00 sec) mysql> update a set d= '20' where a= '2' ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from a; + ----+------+------+------+------+ | id | a | b | c | d | + ----+------+------+------+------+ | 1 | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | 20 | | 3 | 3 | 3 | 3 | 3 | | 4 | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | 5 | | 6 | 6 | 6 | 6 | 6 | + ----+------+------+------+------+ #此时事务1执行 commit 操作 mysql> select * from a; + ----+------+------+------+------+ | id | a | b | c | d | + ----+------+------+------+------+ | 1 | 1 | 1 | 1 | 10 | | 2 | 2 | 2 | 2 | 20 | | 3 | 3 | 3 | 3 | 3 | | 4 | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | 5 | | 6 | 6 | 6 | 6 | 6 | + ----+------+------+------+------+ 6 rows in set (0.00 sec)

在RC隔离级别下，并没有锁表，可以看到，事务1在commit情况下，事务2立刻就可以看到事务1更新的数据，这就是不可重复读。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 mysql> select * from INNODB_TRX\G *************************** 1. row *************************** trx_id: 14316 trx_state: RUNNING trx_started: 2022-04-23 18:08:55 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 3 trx_mysql_thread_id: 57 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 1128 trx_rows_locked: 2 trx_rows_modified: 1 trx_concurrency_tickets: 0 trx_isolation_level: READ COMMITTED trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 trx_schedule_weight: NULL *************************** 2. row *************************** trx_id: 14315 trx_state: RUNNING trx_started: 2022-04-23 18:08:04 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 3 trx_mysql_thread_id: 55 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 1128 trx_rows_locked: 1 trx_rows_modified: 1 trx_concurrency_tickets: 0 trx_isolation_level: READ COMMITTED trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 trx_schedule_weight: NULL 2 rows in set (0.00 sec)