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

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

結論：

1.RR隔離級別并發(fā)性沒有RC好

2、開發(fā)過程中，事務要盡量小，結束要快

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 |

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';---注意這里事務依然沒有結束

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;

+----------------+----------------+--------------------------------------+-----------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| 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

小結如下：1、開發(fā)過程中，事務要盡量小，結束要快

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://www.rwnh.cn/article32/phodsc.html

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

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

猜你還喜歡下面的內容

内射老阿姨1区2区3区4区_久久精品人人做人人爽电影蜜月_久久国产精品亚洲77777_99精品又大又爽又粗少妇毛片

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