MYSQL RC 和RR隔離級(jí)別差異性(無(wú)索引)
今天一個(gè)朋友咨詢我關(guān)于MYSQL 的LOCK，我針對(duì)他的問(wèn)題，整理了一篇BLOG，供大家了解學(xué)習(xí)，有興趣的同學(xué)可以參考來(lái)測(cè)試加深原理的理解。
結(jié)論：
1.RR隔離級(jí)別并發(fā)性沒(méi)有RC好
2、開(kāi)發(fā)過(guò)程中，事務(wù)要盡量小，結(jié)束要快
3、需要?jiǎng)?chuàng)建合適的索引來(lái)減少全表掃的概率
RR隔離級(jí)別的詭異現(xiàn)象，RC隔離級(jí)別比RR隔離級(jí)別的并發(fā)性好
1、隔離級(jí)別為RR 查看如下:
mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
2、創(chuàng)建測(cè)試表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、開(kāi)啟會(huì)話1 執(zhí)行如下語(yǔ)句，由于自動(dòng)提交是開(kāi)啟的，所以這里使用start transaction或者begin開(kāi)啟一個(gè)事務(wù)
查看是否開(kāi)啟自動(dòng)提交：
mysql> show variables like
-> '%auto%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |--自動(dòng)提交
| 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';---注意這里事務(wù)依然沒(méi)有結(jié)束
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
4、開(kāi)啟會(huì)話2，做插入語(yǔ)句，此時(shí)語(yǔ)句2直接掛起直到會(huì)話1提交或者等待INNODB超時(shí)時(shí)間自動(dòng)回滾，
查看INNODB 超時(shí)時(shí)間（這里默認(rèn)是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 |--默認(rèn)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
可見(jiàn)插入語(yǔ)句無(wú)法執(zhí)行成功。在測(cè)試中我們來(lái)分別觀察information_schema.innodb_trx,information_schema.innodb_locks,information_schema.innodb_lock_waits
利用查鎖語(yǔ)句：可見(jiàn)152會(huì)話被151會(huì)話堵塞了，152會(huì)話執(zhí)行的INSERT INTO 語(yǔ)句，151會(huì)話目前執(zhí)行的查鎖語(yǔ)句；
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ù)如下：可見(jiàn)會(huì)話152，插入語(yǔ)句堵塞了，會(huì)話151，UPDATE語(yǔ)句鎖定了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；
測(cè)試到這里，大家應(yīng)該可以發(fā)現(xiàn)隔離級(jí)別在RR情況下，并發(fā)性不好，那原因是什么呢？
原理如下：當(dāng)表沒(méi)有利用上二級(jí)索引的情況下或者沒(méi)有索引的情況下（我測(cè)試是沒(méi)有創(chuàng)建二級(jí)索引，當(dāng)掃描的數(shù)據(jù)超過(guò)表數(shù)據(jù)的20%以上可能導(dǎo)致走不上索引即全表掃），MYSQL會(huì)做全表掃描，這個(gè)時(shí)候會(huì)鎖定全表，即會(huì)導(dǎo)致無(wú)法對(duì)該表做任何DML操作參考，我這里只列出來(lái)了插入語(yǔ)句堵塞，有興趣的可以看看DELETE和UPDATE是否也被堵塞，其實(shí)從上面可以觀察到是一定的。（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、開(kāi)發(fā)過(guò)程中，事務(wù)要盡量小，結(jié)束要快
2、需要?jiǎng)?chuàng)建合適的索引來(lái)減少全表掃的概率
2、修改隔離級(jí)別，臨時(shí)性修改如下（如果永久性修改需要修改my.cnf文件）這里修改完了切記退出會(huì)話重新登錄。
mysql> set global tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
3、隔離級(jí)別是RC情況下測(cè)試
查看隔離級(jí)別：
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、會(huì)話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、會(huì)話2執(zhí)行下：沒(méi)有出現(xiàn)堵塞
mysql> insert into t_test4 values(4,'zhej');
Query OK, 1 row affected (0.00 sec)
可以觀察這個(gè)時(shí)候這里只鎖定了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--隔離級(jí)別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)
