再谈事务
上一篇咱们简单了解了事务,这一篇我们再深入一下吧
Ⅰ、事务隔离级别
事务一共有四种隔离级别
| 简称 | 全称 | - |
|---|---|---|
| ru | read-uncommited | 未提交读 |
| rc | read-committed | 提交读 |
| rr | repeatable-read | 可重复读 |
| srz | serializable | 可串行 |
从真正意义上来看只有srz达到真正隔离性的要求
oracel、sqlserver默认rc,mysql默认rr(99.99%达到隔离性要求)
事务隔离级别越低,事务请求的锁越少或者保持锁的时间就越短
1.1 四种隔离级别解读
事务隔离级别解决了三个问题,脏读,不可重复读,幻读
①read-uncommitted
可以读到其他线程未提交的数据,没人用,这就是脏读
②read-committed
解决了脏读,不会读到其他线程未提交的数据
但是a线程事务开始没提交,b线程读不到对应的数据,a线程事务提交后,b读到了
这就是不可重复读,两次读到不一样,破坏了隔离性,一个线程的事务所做的修改被另一个线程可见了
③repeatable-read
解决不可重复读,一边提交了,另一边还是看不到,两次读结果一样
重复读还可以解决一个问题,幻读(读到之前不存在的记录,讲锁的时候再演示)
例外情况：
- for update锁定读就可以读到第一个线程中事务提交的数据,可以说是幻读,也可以说是一个不可重复读,这就是99.999%,哈哈
- 这里也是一种不符合隔离性的,读到了之前不存在的记录,只要是支持mvcc就很难做到完全隔离性
④serializable
两阶段加锁可串行化保证隔离性：加锁阶段,只加锁不放锁,解锁阶段,只放锁,不解锁
对于所有的写,每行上面都有锁,会有大量的锁竞争和超时
这样就失去了MVCC特性(非锁定一致性读)
所有操作都要加共享锁,lock in share mode ,执行selct * from xxx;
其实重写为select * from xxx lock in share mode; 对这条记录加共享锁,
强制事务排序,使得不会互相冲突,这样就不存在并发问题,都是串行了,读写相互阻塞
1.2 好奇心,我们对比下rr和sr
RR隔离级别
sesion1:
mysql> set tx_isolation="REPEATABLE-READ";
Query OK, 0 rows affected (0.00 sec)
mysql> create table t_lock(a int, b int, primary key(a));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into t_lock values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_lock;
+---+------+
| a | b |
+---+------+
| 1 | 1 |
+---+------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.02 sec)
mysql> select b from t_lock where a=1;
+------+
| b |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
session2:
mysql> set tx_isolation="REPEATABLE-READ";
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t_lock set b=2 where a = 1;
Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
session1:
mysql> select b from t_lock where a=1; -- 再执行一次,得到的结果是1
+------+
| b |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select b from t_lock where a=1 for update; -- for update的去读,得到的结果是2
+------+
| b |
+------+
| 2 |
+------+ 1 row in set (0.00 sec)
session1中, RR隔离级别下,前两次读都是读取的快照,最后一次读取的当前更新的值
SR隔离级别
session1:
mysql> select * from t_lock;
+---+------+
| a | b |
+---+------+
| 1 | 1 |
+---+------+
1 row in set (0.00 sec)
mysql> set tx_isolation='SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select b from t_lock where a=1;
+------+
| b |
+------+
| 1 |
+------+ 1
row in set (0.00 sec)
session2:
mysql> show engine innodb status\G
-- ------------省略部分输出------------
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 3, OS thread handle 0x7f946bc94700, query id 30 localhost root cleaning up
TABLE LOCK table `burn_test`.`t_lock` trx id 5390 lock mode IS
RECORD LOCKS space id 15 page no 3 n bits 72 index `PRIMARY` of table `burn_test`.`t_lock` trx id 5390 lock mode S locks rec but not gap -- 有S锁
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000000150c; asc ;;
2: len 7; hex 8c000000340110; asc 4 ;;
3: len 4; hex 80000001; asc ;;
mysql> set tx_isolation='SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t_lock set b=2 where a=1; -- 在SR的隔离级别下,直接阻塞,因为a=1上有一个S锁
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
SERIALIZABLE的隔离级别一般来说是不会去用
1.3 事务隔离级别的选择
show variables like 'tx_isolation';
set global 后只对之后创建的session生效,之前的session没用
如何查看当前各个线程的事务隔离级别,sys库里面的session表看不了啊？
应该看performance_schema中user_variables_by_thread表
select * from user_variables_by_thread where variable_name = 'tx_isolation';
找到thread_id
select processlist_id from threads where thread_id in(xxx);
再对应到processlist id
重点：
- 我们生产中一定要用rc,不要用默认的rr,一般场景rc性能会好很多,rc的锁好理解
- 为了同步数据一致性binlog_format用row,mixed会遇到各种bug导致主从不一致,5.7官方默认用row了,没有协商的余地,组复制也必须用row(目前MySQL若启用rc,不管binlog_format怎么设置都会强行转为row)
transaction_isolation = read-committed
binlog_format = row
上面两个一定要配到my.cnf中,没有任何理由
- 有一种情况rr比rc好,条件是,应用偏读,一个事务里面有10个select,后面会提到,read_view的原因
