MYSQL RC 和RR隔離級別差異性(無索引)
今天一個朋友咨詢我關于MYSQL 的LOCK，我針對他的問題，整理了一篇BLOG，供大家了解學習，有興趣的同學可以參考來測試加深原理的理解。
結論：
1.RR隔離級別并發性沒有RC好
2、開發過程中，事務要盡量小，結束要快
3、需要創建合適的索引來減少全表掃的概率
RR隔離級別的詭異現象，RC隔離級別比RR隔離級別的并發性好
1、隔離級別為RR 查看如下:
mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
2、創建測試表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 執行如下語句，由于自動提交是開啟的，所以這里使用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';---注意這里事務依然沒有結束
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
可見插入語句無法執行成功。在測試中我們來分別觀察information_schema.innodb_trx,information_schema.innodb_locks,information_schema.innodb_lock_waits
利用查鎖語句：可見152會話被151會話堵塞了，152會話執行的INSERT INTO 語句，151會話目前執行的查鎖語句；
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 |
數據如下：可見會話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；
測試到這里，大家應該可以發現隔離級別在RR情況下，并發性不好，那原因是什么呢？
原理如下：當表沒有利用上二級索引的情況下或者沒有索引的情況下（我測試是沒有創建二級索引，當掃描的數據超過表數據的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
小結如下：1、開發過程中，事務要盡量小，結束要快
2、需要創建合適的索引來減少全表掃的概率
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執行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執行下：沒有出現堵塞
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---鎖定記錄數
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)
