加入收藏 | 设为首页 | 会员中心 | 我要投稿 湘西站长网 (https://www.0743zz.cn/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

MYSQL RC 和RR隔离等级差异性

发布时间:2022-03-27 08:11:32 所属栏目:MySql教程 来源:互联网
导读:继续就上一篇比较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隔离不存在这种情况。
 

(编辑:湘西站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读