事务的隔离级别有 4 种: SQL-1992 ,但是我只想介绍其中两种,因为其他的两个根本就用不上
- 1. 读数据的时候,会去加S-lock、x-lock
- 2. eg:select ... for update , select ... lock in share mode
- 3. dml语句
- 1. 读数据的时候,不加任何的锁,快照读(snapshot read)
- 2. eg: select ... 最普通的查询语句
- 一个事务内的同一条【一致性锁定读】SQL多次执行,读到的结果不一致,我们称之为幻读。
- * set global tx_isolation='READ-COMMITTED'
- > 事务一:
- root:test> begin;select * from lc for update;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- +------+
- > 事务二:
- root:test>begin; insert into lc values(3);
- Query OK, 1 row affected (0.00 sec)
- root:test> commit ;
- Query OK, 0 rows affected (0.00 sec)
- > 事务一:
- root:test> select * from lc for update;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- | 3 |
- +------+
- 3 rows in set (0.00 sec)
- * 同一个事务一中,同一条select * from lc for update (一致性锁定读) 执行两次,得到的结果不一致,说明产生了幻读
- * 同一个事务一中,同一条select * from lc (一致性非锁定读) 执行两次,得到的结果不一致,说明产生了幻读
- * 我们姑且认为,幻读和不可重复读为一个概念,实际上也差不多一个概念。
- 1. 这个大家都很多好理解,就是事务一还没有提交的事务,却被事务二读到了,这就是脏读
- 1. 学名: 可重复读
- 2. 顾名思义:一个事务内的同一条【一致性锁定读】SQL多次执行,读到的结果一致,我们称之为可重复读。
- 3. 解决了幻读的问题
- * 学名:可提交读
- * 顾名思义: 只要其他事务提交了,我就能读到
- * 解决了脏读的问题,没有解决幻读的问题
就拿上面那个简单的例子来佐证好了
- dba:lc_4> show create table lc;
- +-------+--------------------------------------------------------------------------------------------------------+
- | Table | Create Table |
- +-------+--------------------------------------------------------------------------------------------------------+
- | lc | CREATE TABLE `lc` (
- `id` int(11) NOT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
- +-------+--------------------------------------------------------------------------------------------------------+
- 1 row in set (0.00 sec)
- dba:lc_4> select * from lc;
- +----+
- | id |
- +----+
- | 1 |
- | 2 |
- | 3 |
- +----+
- 3 rows in set (0.00 sec)
RR 如何解决幻读问题? RR 的锁算法:next-key lock
- dba:lc_4> set tx_isolation='repeatable-read';
- Query OK, 0 rows affected (0.00 sec)
- dba:lc_4> select * from lc for update ;
- +----+
- | id |
- +----+
- | 1 |
- | 2 |
- | 3 |
- +----+
- 3 rows in set (0.00 sec)
- 这时候,查看下锁的情况:
- ------------
- TRANSACTIONS
- ------------
- Trx id counter 133588361
- Purge done for trx's n:o < 133588356 undo n:o < 0 state: running but idle
- History list length 892
- LIST OF TRANSACTIONS FOR EACH SESSION:
- ---TRANSACTION 421565826150000, not started
- 0 lock struct(s), heap size 1136, 0 row lock(s)
- ---TRANSACTION 421565826149088, not started
- 0 lock struct(s), heap size 1136, 0 row lock(s)
- ---TRANSACTION 133588360, ACTIVE 4 sec
- 2 lock struct(s), heap size 1136, 4 row lock(s)
- MySQL thread id 135, OS thread handle 140001104295680, query id 1176 localhost dba cleaning up
- TABLE LOCK table `lc_4`.`lc` trx id 133588360 lock mode IX
- RECORD LOCKS space id 289 page no 3 n bits 72 index PRIMARY of table `lc_4`.`lc` trx id 133588360 lock_mode X --next key lock , 锁记录和范围
- Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
- 0: len 8; hex 73757072656d756d; asc supremum;; --next-key lock, 锁住正无穷大
- Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
- 0: len 4; hex 80000001; asc ;; --next-key lock, 锁住1和1之前的区间,包括记录 (negtive,1]
- 1: len 6; hex 000007f6657e; asc e~;;
- 2: len 7; hex e5000040220110; asc @" ;;
- Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
- 0: len 4; hex 80000002; asc ;; --next-key lock, 锁住2和1之前的区间,包括记录 (1,2]
- 1: len 6; hex 000007f6657f; asc e ;;
- 2: len 7; hex e6000040330110; asc @3 ;;
- Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
- 0: len 4; hex 80000003; asc ;; --next-key lock, 锁住3和2之间的区间,包括记录 (2,3]
- 1: len 6; hex 000007f66584; asc e ;;
- 2: len 7; hex e9000040240110; asc @$ ;;
- * 总结下来就是:
- 1. (negtive bounds,1] , (1,2] , (2,3],(3,positive bounds) --锁住的记录和范围,相当于表锁
- 2. 这时候,session 2 插入任何一条记录,会被锁住,所以幻读可以避免,尤其彻底解决了幻读的问题
RC 的锁算法:record locks 幻读对线上影响大吗? oracle 默认就是 RC 隔离级别
- dba:lc_4> set tx_isolation='read-committed';
- Query OK, 0 rows affected (0.00 sec)
- dba:lc_4> select * from lc for update ;
- +----+
- | id |
- +----+
- | 1 |
- | 2 |
- | 3 |
- +----+
- 3 rows in set (0.00 sec)
- * 查看锁的信息如下
- ------------
- TRANSACTIONS
- ------------
- Trx id counter 133588362
- Purge done for trx's n:o < 133588356 undo n:o < 0 state: running but idle
- History list length 892
- LIST OF TRANSACTIONS FOR EACH SESSION:
- ---TRANSACTION 421565826150000, not started
- 0 lock struct(s), heap size 1136, 0 row lock(s)
- ---TRANSACTION 421565826149088, not started
- 0 lock struct(s), heap size 1136, 0 row lock(s)
- ---TRANSACTION 133588361, ACTIVE 3 sec
- 2 lock struct(s), heap size 1136, 3 row lock(s)
- MySQL thread id 138, OS thread handle 140001238955776, query id 1192 localhost dba cleaning up
- TABLE LOCK table `lc_4`.`lc` trx id 133588361 lock mode IX
- RECORD LOCKS space id 289 page no 3 n bits 72 index PRIMARY of table `lc_4`.`lc` trx id 133588361 lock_mode X locks rec but not gap --记录锁,只锁记录
- Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
- 0: len 4; hex 80000001; asc ;; -- 记录锁,锁住1
- 1: len 6; hex 000007f6657e; asc e~;;
- 2: len 7; hex e5000040220110; asc @" ;;
- Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
- 0: len 4; hex 80000002; asc ;; -- 记录锁,锁住2
- 1: len 6; hex 000007f6657f; asc e ;;
- 2: len 7; hex e6000040330110; asc @3 ;;
- Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
- 0: len 4; hex 80000003; asc ;; -- 记录锁,锁住3
- 1: len 6; hex 000007f66584; asc e ;;
- 2: len 7; hex e9000040240110; asc @$ ;;
- * 总结下来
- 1. 锁住的是哪些? [1,2,3] 这些记录被锁住
- 2. 那么session 2 除了1,2,3 不能插入之外,其他的记录都能,比如; insert into lc select 4 , 那么再次select * from lc for udpate 的时候,就是4条记录了,由此产生幻读
- 1. RC 模式,binlog 必须使用Row 模式
- * session 1:
- begin;
- delete from tb_1 where id > 0;
- * session 2:
- begin;
- insert into tb_1 select 100;
- commit;
- * session 1:
- commit;
- * 如果RC模式下的binlog是statement模式,结果会是怎么样呢?
- master : 结果是 100
- slave : 结果是 空
- 这样就导致master和slave结果不一致了: 因为在slave上,先执行insert into tb_1 select 100; 再执行delete from tb_1 where id > 0; 当然等于空咯
- * 如果RC模式下的binlog是ROW模式,结果会是怎么样呢?
- master : 结果是 100
- slave : 结果是 100
- 主从结果一致,因为binlog是row模式,slave并不是逻辑的执行上述sql,而记录的都是行的变化
- 1. 由于降低了隔离级别,那么实现起来简单,对锁的开销小,基本上不会有Gap lock,那么导致死锁和锁等待的可能就小
- 2. 当然RC也不是完全没有Gap lock,当purge 和 唯一性索引存在的时候会产生特殊的Gap lock,这个后面会具体讲
- 1. 会有幻读发生
- 2. 事务内的每条select,都会产生新的read-view,造成资源浪费
- 1. 一个事务,只有再开始的时候才会产生read-view,有且只有一个,所以这块消耗比较小
- 2. 解决了幻读的问题, 实现了真正意义上的隔离级别
- 1. 由于RR的实现,是通过Gap-lock实现,经常会锁定一个范围,那么导致死锁和所等待的概率非常大
- 一般我们生产环境的标配,都是RC+Row 模式,谁用谁知道哦
来源: https://yq.aliyun.com/articles/103143