MYSQLRC和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)