mysql事务隔离级别
mysql事务隔离级别
介绍
事务(transaction)指的是一组SQL语句,它们是一个执行单元,且在必要时还可以取消.
mysql在执行每一条SQL语句时,会自动对该语句所涉及的资源进行锁定,所以单条SQL语句不需要使用事务.
事务把多条语句定义为一个执行单元,便可防止在多客户端环境里可能会发生的并发问题.
事务的四个特征:ACID.
- A(atomicity),原子性.
- C(consistency),一致性.
- I(isolation),隔离性.
- D(durability),持久性.
原子性
构成事务的所有语句应该是一个独立的逻辑单元.你不能只执行它们当中的一部分.
一致性
数据库在执行前后都必须是一致的.比如说事务里要插入一条语句,而此语句有外键约束user_id,并且user_id的值不存在,那么此事务会执行失败并回滚.
一致性更多需要用户自己负责处理.
隔离性
事务之间不应该相互影响.
持久性
当事务执行完成时,其影响将被永久的记录在数据库里.
隔离性
以mysql的InnoDB为例,默认为REPEATABLE-READ.
| 隔离级别 | 说明 | 脏读 | 不可重复读 | 幻读 | 解释 |
|---|---|---|---|---|---|
| READ UNCOMMITTED | 读未提交 | 是 | 是 | 是 | 一个事务可以读取另一个事务并未提交的更新结果 |
| READ COMMITTED | 读已提交 | 否 | 是 | 是 | 一个事务的更新操作结果只有在该事务提交之后，另一个事务才可以的读取到同一笔数据更新后的结果。 |
| REPEATABLE READ | 重复读 | 否 | 否 | 否 | 整个事务过程中，对同一笔数据的读取结果是相同的，不管其他事务是否在对共享数据进行更新，也不管更新提交与否。 |
| SERIALIZABLE | 序列化 | 否 | 否 | 否 | 所有事务操作依次顺序执行 |
脏读
一个事务读取到另一个事务未提交的数据,而结果却不一致.
原因另一事务未持久化前,发生回滚操作.
解决数据持久化后才可读取,基于撤消日志重建数据.
理解事务的新增,修改操作都会影响结果.
比如算营业额,用户支付完了,但后面退款了,此笔金额数据即为脏数据.
在事务中,它是以一组SQL语句为单元,都执行成功便会提交,提交完了就允许持久化了,所以事务中的脏数据好解决,但业务中的很难.
不可重复读
同一个事务两次执行同一条select语句(有唯一索引或范围搜索),结果不同.
原因另一个事务在当前事务两次执行期间修改了某些行.
解决
- select非锁定语句:在第一次读取建立快照(MVVC),从而保证多次读取结果是一致的,快照以时间为界.
- select锁定语句:如果是唯一索引则锁定索引记录,其他的范围搜索条件InnoDB使用间隙锁(gap locks)或下一键锁定(next-key locks)解决.
理解事务的修改会影响结果,重点在于范围否则也会出现幻读的情况,关注的是修改.
幻读
同一个事务两次执行同一条select语句(如全表查询),结果不同.
原因另一个事务在当前事务两次执行期间新增了某些行.
解决
- 通过多版本并发控制(MVCC)解决幻读问题,MVCC是乐观锁的一种实现机制.
理解事务的新增会影响结果,幻读是不可重复读的一种情况,关注的是新增.
InnoDB默认为REPEATABLE-READ
使用时间点创建快照,如果数据被另一个事务修改或新增(但未提交),使用日志撤消并重建数据,这样就解决了脏读的问题.
select的非锁定语句直接返回快照,有范围的select的锁定语句使用间隙锁,从而解决了不可重复读的问题.
如果没有范围select锁定语句使用表锁,从而解决幻读的问题.
查看事务的隔离级别
mysql8
// 查看隔离级别
select @@transaction_isolation;
// 查看系统的隔离级别
SELECT @@GLOBAL.transaction_isolation, @@GLOBAL.transaction_read_only;
// 查看会话的隔离级别
SELECT @@SESSION.transaction_isolation, @@SESSION.transaction_read_only;
查询出来的值为带连接符的,但设置的时候不需要使用连接符.
READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
mysql5.7
// 查看当前会话的隔离级别
select @@tx_isolation;
// 查看系统的隔离级别
select @@global.tx_isolation;
设置事务的隔离级别
- GLOBAL,后续所有会话都会受影响,当前会话不受影响.
- SESSION,当前会话会受影响.
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
select @@transaction_isolation;
START TRANSACTION;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
COMMIT;
表锁和范围锁
假设表docs共有20条数据.
不加范围则执行表锁,加范围则执行间隙锁,最后一行不要包含(next-key locks).
在锁定的情况下,非锁定语句是可以正常执行的,因为它不需要获取锁定权限.
表锁其他锁定语句只能等待.
START TRANSACTION;
update docs set title = 'moments';
因为没有加范围所以下面的插入语句只能等待上一事务执行完成(COMMIT).
insert into docs (title) values ('pythonschool');
间隙锁不在范围内的锁定语句可正常执行.
START TRANSACTION;
update docs set title = 'moments' where id < 20; // 最后一行不要包含
因为有范围所以下面的插入语句可以直接执行不用等待上一事务执行完成(COMMIT).
insert into docs (title) values ('pythonschool');
多版本并发控制
英文全称为Multi-Version Concurrency Control,乐观锁为理论基础的MVCC(多版本并发控制).
mysql中,默认的事务隔离级别是可重复读(repeatable-read),为了解决不可重复读, innodb采用了MVCC来解决这一问题。
MVCC是利用在每条数据后面加了隐藏的两列(创建版本号和删除版本号), 每个事务在开始的时候都会有一个递增的版本号.
多版本-即多条记录,这样InnoDB在创建快照的时候也是多版本的.
并发控制-乐观锁,加上版本号,决定匹配哪一个版本.