MYSQL RC 和RR隔离级别差异性(有合适索引)

千家信息网最后更新 2024年11月18日MYSQL RC 和RR隔离级别差异性(有合适索引)继续就上一篇比较RC 和RR隔离级别的差异性，有合适索引的比较：
1、隔离级别是RR，在t_test4表上面添加合适的索引即name列添加二级索引 会话158 查看隔离级别和在name 列创建索引 mysql> show variables like '%iso%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.01 sec)
mysql> select * from t_test4 order by name; +------+-------+ | id | name | +------+-------+ | 6 | hubei | | 5 | wuhan | | 2 | zhej | | 4 | zhej | | 4 | zhej | | 4 | zhej | | 5 | zhej | +------+-------+ 7 rows in set (0.00 sec)
mysql> create index idx_name on t_test4(name); Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from t_test4; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t_test4 | 1 | idx_name | 1 | name | A | 7 | NULL | NULL | YES | BTREE | | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.01 sec)


---查看UPDATE语句执行计划是否走了新创建的索引idx_name mysql> explain update id=7 where name='hubei'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '7 where name='hubei'' at line 1 mysql> explain update t_test4 set id=7 where name='hubei'; +----+-------------+---------+-------+---------------+----------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+----------+---------+-------+------+-------------+ | 1 | SIMPLE | t_test4 | range | idx_name | idx_name | 23 | const | 1 | Using where | +----+-------------+---------+-------+---------------+----------+---------+-------+------+-------------+ 1 row in set (0.01 sec)
--开启事务 mysql> begin; Query OK, 0 rows affected (0.00 sec)

mysql> update t_test4 set id=7 where name='hubei'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
会话159执行INSERT INTO SQL 等待超时报错 mysql> insert into t_test4 values(8,'hubei'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
查看锁信息：可见158会话堵塞了159会话 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 | +----------------+----------------+---------------------------------------+-----------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 579773 | 159 | insert into t_test4 values(8,'hubei') | 579770 | 158 | 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 | 查看158会话事务信息： mysql> select * from information_schema.innodb_trx\G *************************** 1. row *************************** trx_id: 579770 trx_state: RUNNING trx_started: 2017-09-03 03:49:43 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 5 trx_mysql_thread_id: 158 trx_query: select * from information_schema.innodb_trx trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 4 trx_lock_memory_bytes: 1184 trx_rows_locked: 3--锁定了3条记录 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 1 row in set (0.00 sec)
原因是什么呢？是因为在RR隔离级别下，为了保证可重复读，MySQL引入了GAP锁，什么是GAP锁呢？先来看看定义： A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked. gap是索引记录之间的锁，在第一个满足索引记录之前和最后一个满足索引记录之后。如下图（测试例子）这里重点仔细看哦 所以我插入hubei插入不了，另外 下面来看看GAP是否如上图所示，hubei之前无法插入数据,hubei和wuhan之间无法插入数据，wuhan之后可以正常插入： mysql> insert into t_test4 values(8,'hu'); --失败 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into t_test4 values(8,'hubei');--失败 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into t_test4 values(8,'wuhan'); --成功 Query OK, 1 row affected (0.01 sec)
下面来看看RC隔离级别是否会出现这种情况（修改隔离级别之后记得退出重新登录） 会话1： mysql> set global tx_isolation='READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec)
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> start transaction; Query OK, 0 rows affected (0.00 sec)
mysql> update t_test4 set id=8 where name='hubei'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

会话2 mysql> insert into t_test4 values(8,'hu'); Query OK, 1 row affected (0.01 sec)
mysql> insert into t_test4 values(8,'hubei'); Query OK, 1 row affected (0.00 sec)
mysql> insert into t_test4 values(8,'hubei1'); Query OK, 1 row affected (0.00 sec)
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 579785
trx_state: RUNNING
trx_started: 2017-09-03 04:29:57
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 4
trx_mysql_thread_id: 168
trx_query: select * from information_schema.innodb_trx
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 3
trx_lock_memory_bytes: 360
trx_rows_locked: 2
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
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)

可见RC隔离不存在这种情况。

小结：

隔离级别	无合适索引	有合适索引
RC	只锁定需要更新的记录	只锁定需要更新的记录
RR	会锁定所有的记录	由于GAP锁所以需要锁定索引记录之间的锁，会多锁定记录