MySQL中隔離級別RC與RR的區別及說明

2022-08-17 14:03:23

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再次讀取該資料時資料已經發生改變。

可重複讀：是指在一個事務內多次讀同一資料。假設在一個事務還沒結束時，另一個事務也存取同一資料，那麼在第一個事務中的兩次讀資料之間，即使第二個事務對資料進行了修改，第一個事務兩次讀到的資料也是一樣的。這樣在一個事務內兩次讀到的資料就是一樣的，因此稱為可重複讀。讀取資料的事務禁止寫事務（但允許讀事務），寫事務則禁止任何其他事務，這樣即可避免不可重複讀和髒讀，但是有時可能出現幻讀。

序列化：提供嚴格的事務隔離。它要求事務序列化執行，即事務只能一個接著一個地執行，但不能並行執行。僅僅通過“行級鎖”是無法實現事務序列化的，必須通過其他機制保證新插入的資料不會被剛執行查詢操作的事務存取到。序列化是最高的事務隔離級別，同時代價也最高，效能很低，一般很少使用。在該級別下，事務順序執行，不僅可以避免髒讀、不可重複讀、還避免了幻讀

一、建立測試資料

建立測試表：

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;

插入資料

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');

查詢資料：

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)

建立索引：

CREATE INDEX index_b_c ON a(b,c);

二、RR隔離級別

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

可以從上圖看出，目前MySQL處於RR的隔離級別（可重複讀）

事務1：

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：

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

等待時間取決於這個引數：

innodb_lock_wait_timeout

檢視mysql鎖情況：

PS：因為show processlist只會顯示出當前正在執行的語句，有些語句很快就執行完成了，所以有時候是看不全的，需要通過系統表檢視

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

mysql> select * from INNODB_TRXG
*************************** 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

mysql> select * from data_lock_waitsG
*************************** 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的不是同一條資料，還是會鎖全表呢？

帶著這個疑問我們來看，下面來看第二個例子：

開啟第一個事務：

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

開啟第二個事務：

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鎖情況：

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

mysql> select * from INNODB_TRXG
*************************** 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

mysql> select * from performance_schema.data_lock_waitsG
Empty set (0.00 sec)

鎖不存在

事務1提交：

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提交：

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

mysql> set global transaction_isolation='read-committed'

事務1：

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：

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更新的資料，這就是不可重複讀。

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:1067:140166526216544   |                 14315 |       112 |       22 | test          | a           | NULL           | NULL              | NULL       |       140166526216544 | TABLE     | IX            | GRANTED     | NULL      |
| INNODB | 140166601010392:6:4:10:140166526213552 |                 14315 |       112 |       22 | test          | a           | NULL           | NULL              | PRIMARY    |       140166526213552 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)

mysql> select * from INNODB_TRXG
*************************** 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)

兩個事務都正常執行。

mysql> select * from data_lock_waitsG
Empty set (0.00 sec)

並沒有鎖表

結論：RC隔離級別下，其實是順序的給每一行都加了鎖的（不是update的資料的話，就馬上解開），但是速度非常快，如果資料量足夠大的話，也是會有lock的。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。