MYSQL RC 和RR隔离等级差异性
发布时间：2022-03-26 19:53:41 所属栏目：编程 来源：互联网
导读：继续就上一篇比较RC 和RR隔离级别的差异性，有合适索引的比较： 会话158 查看隔离级别和在name 列创建索引 mysql show variables like %iso%; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_iso
|
继续就上一篇比较RC 和RR隔离级别的差异性，有合适索引的比较： 会话158 查看隔离级别和在name 列创建索引 mysql> show variables like '%iso%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.01 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) --开启事务 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_trxG *************************** 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) 下面来看看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_trxG *************************** 1. row *************************** 可见RC隔离不存在这种情况。 （编辑：核心网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
