MYSQLRC和RR隔離級別差異性(無索引)

 今天一個朋友咨詢我關(guān)于MySQL 的LOCK，我針對他的問題，整理了一篇BLOG，供大家了解學習，有興趣的同學可以參考來測試加深原理的理解。

結(jié)論：
1.RR隔離級別并發(fā)性沒有RC好
2、開發(fā)過程中，事務要盡量小，結(jié)束要快
3、需要創(chuàng)建合適的索引來減少全表掃的概率


RR隔離級別的詭異現(xiàn)象，RC隔離級別比RR隔離級別的并發(fā)性好

1、隔離級別為RR 查看如下:
mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
2、創(chuàng)建測試表t_test4且插入4條記錄
create table t_test4(id int,name varchar(20));
INSERT INTO T_TEST4 VALUES(4，'wuhan');
......
......
mysql> select * from t_test4;
+------+-------+
| id | name |
+------+-------+
| 4 | wuhan |
| 2 | zhej |
| 4 | zhej |
| 4 | zhej |
3、開啟會話1 執(zhí)行如下語句，由于自動提交是開啟的，所以這里使用start transaction或者begin開啟一個事務
查看是否開啟自動提交：
mysql> show variables like
-> '%auto%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |--自動提交
| automatic_sp_privileges | ON |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_stats_auto_recalc | ON |
| sql_auto_is_null | OFF |
+-----------------------------+-------+
8 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update t_test4 set id=4 where name='wuhan';---注意這里事務依然沒有結(jié)束
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

4、開啟會話2，做插入語句，此時語句2直接掛起直到會話1提交或者等待INNODB超時時間自動回滾，
查看INNODB 超時時間（這里默認是50秒）：
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |--默認50秒
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+-----------------------------+----------+
12 rows in set (0.00 sec)

mysql> insert into t_test4 values(4,'zhej');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可見插入語句無法執(zhí)行成功。在測試中我們來分別觀察information_schema.innodb_trx,information_schema.innodb_locks,information_schema.innodb_lock_waits

利用查鎖語句：可見152會話被151會話堵塞了，152會話執(zhí)行的INSERT INTO 語句，151會話目前執(zhí)行的查鎖語句；

mysql> SELECT
-> r.trx_id waiting_trx_id,
-> r.trx_mysql_thread_id waiting_thread,
-> r.trx_query waiting_query,
-> b.trx_id blocking_trx_id,
-> b.trx_mysql_thread_id blocking_thread,
-> b.trx_query blocking_query
-> FROM information_schema.innodb_lock_waits w
-> INNER JOIN information_schema.innodb_trx b
-> ON b.trx_id = w.blocking_trx_id
-> INNER JOIN information_schema.innodb_trx r
-> ON r.trx_id = w.requesting_trx_id;
+----------------+----------------+--------------------------------------+-----------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+--------------------------------------+-----------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 579724 | 152 | insert into t_test4 values(4,'zhej') | 579720 | 151 |
SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id |

數(shù)據(jù)如下：可見會話152，插入語句堵塞了，會話151，UPDATE語句鎖定了5行，但是我們只需要修改一行。

mysql> select * from information_schema.innodb_trx \G
*************************** 1. row ***************************
trx_id: 579737
trx_state: LOCK WAIT
trx_started: 2017-09-02 01:29:12
trx_requested_lock_id: 579737:121:3:1
trx_wait_started: 2017-09-02 01:29:12
trx_weight: 2
trx_mysql_thread_id: 152
trx_query: insert into t_test4 values(4,'zhej')
trx_operation_state: inserting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 360
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: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 579733
trx_state: RUNNING
trx_started: 2017-09-02 01:05:27
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 151
trx_query: select * from information_schema.innodb_trx
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 360
trx_rows_locked: 5
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: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0；
測試到這里，大家應該可以發(fā)現(xiàn)隔離級別在RR情況下，并發(fā)性不好，那原因是什么呢？

原理如下：當表沒有利用上二級索引的情況下或者沒有索引的情況下（我測試是沒有創(chuàng)建二級索引，當掃描的數(shù)據(jù)超過表數(shù)據(jù)的20%以上可能導致走不上索引即全表掃），MYSQL會做全表掃描，這個時候會鎖定全表，即會導致無法對該表做任何DML操作參考，我這里只列出來了插入語句堵塞，有興趣的可以看看DELETE和UPDATE是否也被堵塞，其實從上面可以觀察到是一定的。（https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html）If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily scan many rows。
其他參考如下：
https://dev.mysql.com/doc/refman/5.6/en/where-optimization.html 

小結(jié)如下：1、開發(fā)過程中，事務要盡量小，結(jié)束要快
2、需要創(chuàng)建合適的索引來減少全表掃的概率

2、修改隔離級別，臨時性修改如下（如果永久性修改需要修改my.cnf文件）這里修改完了切記退出會話重新登錄。
mysql> set global tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)



3、隔離級別是RC情況下測試
查看隔離級別：
mysql> show global variables like '%iso%';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
mysql> show variables like '%iso%';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

2、會話1執(zhí)行SQL
mysql> begin
-> ;
Query OK, 0 rows affected (0.00 sec)

mysql> update t_test4 set id=5 where name='wuhan';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

3、會話2執(zhí)行下：沒有出現(xiàn)堵塞
mysql> insert into t_test4 values(4,'zhej');
Query OK, 1 row affected (0.00 sec)

可以觀察這個時候這里只鎖定了1行
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 579758
trx_state: RUNNING
trx_started: 2017-09-02 02:33:29
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 155
trx_query: select * from information_schema.innodb_trx
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 360
trx_rows_locked: 1---鎖定記錄數(shù)
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED--隔離級別RC
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

本文題目：MYSQLRC和RR隔離級別差異性(無索引)
網(wǎng)站鏈接：http://muchs.cn/article32/phodsc.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站排名、域名注冊、網(wǎng)站設計、ChatGPT、響應式網(wǎng)站、微信公眾號

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

成都app開發(fā)公司