最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

Mysql事务并发问题与隔离级别深入解析

XAMPP案例 admin 429浏览 0评论

01mysql

本文目录

事务基础

事务控制语句

事务并发问题

  • 脏读
  • 不可重复读
  • 幻读
  • 第一类丢失更新(回滚丢失)
  • 第二类丢失更新(逻辑丢失)

用隔离级别来解决并发问题

 

事务基础

 

事务四大特性ACID:

  • 原子性
  • 一致性
  • 隔离性
  • 持久性
有些数据库可能不会完全遵循ACID,比如oracle的默认隔离级别是read committed,不满足隔离性,但是能带来性能上的提升。mysql的默认隔离级别是read repeatable,完全满足ACID。
这里留下一个伏笔:一般来说,只有将事务隔离级别设置为串行化才能完全满足ACID,为什么Mysql的可重复读就能完全满足ACID呢?别急,下面会讲到。
事务分类:
  • 扁平事务:最常用的;
  • 带有保存点的扁平事务:可以回滚到某一个保存的,而不是回滚到事务最开始的地方;
  • 链事务;
  • 嵌套事务:Mysql不支持,如果有多个begin语句,则后面的begin会隐式提交;
  • 分布式事务;
Mysql InnoDB引擎对于事务的实现:
  • 隔离性:锁
  • 原子性、持久性:redo log
  • 一致性:undo log
不好的事务习惯
  • 在循环中提交事务
  • 使用自动提交
  • 使用自动回滚
什么是长事务?
  • 执行时间比较长的事务,可以转化为小批量事务来处理。
事务控制语句
开启事务
  • begin
  • start transaction
提交事务
  • commit
回滚事务
  • rollback
什么是隐式提交语句?
  • 比如create table类的ddl语句,这类语句有很多,在次不一一列举。
查看提交次数(不会计算隐式提交)
  • show global status like ‘com_commit’;
查看自动提交模式是否开启
  • show variables like ‘%autocommit%’;
sql标准定义的四个隔离级别:
  • read uncommitted
  • read committed
  • repeatable read
  • serializable
InnoDB修改事务隔离级别:
  • set global transaction isolation level 隔离级别
  • set session transaction isolation level 隔离级别
查询事务隔离级别:
  • select @@tx_isolation
  • select @@global.tx_isolation
事务并发问题
脏读
一个事务读到了另一个事务中未提交的数据。
不可重复读
一个事务在未提交之前,多次读取同一数据,但读到的结果却是不一样的,称为不可重复读。发生不可重复读的原因是读到了另一个事务已提交的数据。与脏读的区别是,脏读读的是未提交的数据,而不可重复读读的是已提交的数据。
幻读
一个事务在未提交之前,多次读取数据,第一次读到了n条记录,但第二次却读到了>n或<n条数据。幻读其实也是不可重复读,但幻读侧重的是数据的新增或删除,而不可重复读侧重的是同一行数据的修改。
第一类丢失更新(回滚丢失)
看如下步骤:
  1. 现在有一行数据,某个字段值为200
  2. 事务A开启事务
  3. 事务B开始事务
  4. 事务A将200修改为300,并提交
  5. 事务B将200修改为100,但事务B回滚,回滚之后变为200
最终的值应该是300的,但事务B的回滚使得值重新变为200,覆盖了300,这种现象称为第一类更新丢失。
SQL92没有定义这种现象,标准定义的所有隔离级别都不允许第一类丢失更新发生。
InnoDB通过undo日志回滚,比如我执行了一条delete语句,那么在undo中记载的就是对应的insert语句,如果我执行的是一条update语句,那么undo中记载的就是与之相反的update语句。
事务B回滚时,只会执行自己所产生的undo语句,因此不会覆盖事务A的更新。
第二类丢失更新(逻辑丢失)
首先看如下步骤:
  1. 事务A开启事务,并修改了一行数据;
  2. 事务B开启事务,修改了同一行数据;
  3. 事务A提交;
  4. 事务B提交;
由于数据库的update操作会上X锁,并且只有在事务提交和回滚后才会释放锁,因此第二步操作是会阻塞的,就算是最低级别的隔离也会阻塞。因此两次更新操作是顺序进行的,且第二次更新是在第一次更新提交后才进行的(提交才释放锁),因此数据库本身的排它锁机制保证了不会发生更新丢失。
但这里要说的丢失更新,是逻辑情况下的丢失更新,再看如下步骤:
  1. 事务A开启事务,读取了一行数据,假设该行数据有一个age字段,且age=10;
  2. 事务B开启了事务,读取了同一行数据,即age=10。
  3. 事务A进行修改操作,使新age=原age+10,然后提交。
  4. 事务B进行修改操作,使新age=原age+20,注意原age,事务B使用的是自己一开始读取的10,而不是事务A提交后的20,因此修改后的age=30,然后提交;
上述操作会发生丢失更新,因为如果不发生丢失更新则最终的age=40,但最终的age=30,事务B覆盖了事务A的更新。
解决丢失更新问题需要使用序列化读的方式:
  • 将隔离级别设为序列化读;
  • 对读取操作上排它锁,select … for update;
用隔离级别来解决并发问题
事务并发问题 事务隔离级别
第一类更新丢失
读未提交
脏读
读已提交(MVCC:读最新版本,因此造成了不可重复读)
不可重复读
幻读
可重复读(MVCC:读旧版本;next-key Locking解决幻读)
第二类丢失更新
序列化读
该表能清晰地展示出,相应的隔离级别能解决相应的并发问题。比如读已提交能解决第一类更新丢失和脏读,因为从表格的位置来看它们都在读已提交的上方。
读已提交能解决脏读,而读已提交在MVCC机制中是快照读,会造成不可重复读,可重复度在MVCC机制中是当前读,能解决不可重复读问题。
而第二类丢失更新由于是逻辑丢失,因此只能采用序列化读或对select语句上排它锁才能避免。
可重复读隔离级别利用next-key locking解决幻读请参考我的 Mysql锁原理深入解析 这篇文章!

转载请注明:XAMPP中文组官网 » Mysql事务并发问题与隔离级别深入解析

您必须 登录 才能发表评论!