谈谈MySQL的事务隔离级别

这篇文章能够阐述清楚跟数据库相关的四个概念:事务、数据库读现象、隔离级别、锁机制  

一、事务

先来看下百度百科对数据库事务的定义:

  作为单个逻辑单元执行一系列操作,要么完全执行,要么完全不执行。事务处理可以确保除非事务性单元内的所有操作都成功完成,否则不会永久更新面向数据的资源。

事务有四个属性,称为ACID属性:

1、原子性(Atomicity):事务是一个原子单位,要么全部执行,要么全部不执行。

2、一致性(Consistent):事务的开始和结束,数据都必须保持一致状态。

3、隔离性(isolation):数据库系统提供隔离机制,保证并发事务之间是互相不干扰的。也就意味着事务处理过程中的中间状态对其他的事务是透明的。

4、持久性(Durable):事务完成之后,对数据的修改是永久性的,即使出现系统故障也能够保持。

事务是一系列SQL语句的集合,如果没有事务,会出现什么问题?或者说SQL只能一条一条的单个执行,会出现什么问题?

这个很简单,如果没有事务,我们平时生活中的银行转账就无法操作。

二、数据库读现象

  ACID属性里面有一个是隔离级别,即并发事务之间互相不干扰。互相不干扰只是一个终极状态,且需要消耗巨大的性能。在我们实际应用过程中,是存在很大的灰度空间的:隔离级别有程度的区分。所以如果隔离程度控制的比较弱的话,就会产生脏读不可重复读以及幻读的现象。

1、脏读

事务T1修改某个字段的值,然后事务T2读取该值,此后T1撤销了对该字段的更新,或者更新成另外的值才commit到数据库中,这样T2读取的数据是无效的或者错误的。导致T2依据脏数据所做的操作也是错误的。

思聪同学中午去食堂吃饭,看到窗边的座位被如花同学占有了,思聪认为这个座位已经被占有了,就转身去找其他的座位。不料,如花同学起身离开了。事实是:如花并不是吃饭,而是临时坐在那里等她的约会对象,只是临时小坐一会,并没有真正“commit”。

2、不可重复读

在数据库访问中,一个事务范围内的两次相同的查询却返回了不同的数据。

事务T1读取某一数据,事务T2读取并修改了该数据,T1为了对读取值进行验证而重新读取,却发现得到了不同的结果。

思聪同学中午去食堂吃饭,看到窗边的座位是空的,便屁颠屁颠的跑去打饭,回来后却发现这个座位被如花同学抢去了。

3、幻读

幻读解决了不可重复读的问题,即在同一个事务范围内,两次相同的查询结果是相同的。但是可以新增表中的数据记录。

幻读是指事务T1对表中的数据进行修改,假设修改涉及了表中全部的数据行,同时第二个事务也修改这个表中的数据,这种修改是向表中插入一条新的数据。后面就会出现操作了T1事务的用户发现表中还有没有修改的数据行,仿佛出现了幻觉一样。

思聪同学中午去食堂吃饭,看到窗边的座位是空的,便屁颠屁颠的跑去打饭,回来后窗边的座位还是空的,便很高兴坐上去准备开始吃饭,这时候却发现如花同学搬了一个小板凳坐在旁边狼吞虎咽,思聪顿时没有了胃口。

如果需要解决脏读、不可重复读、幻读等这些数据库读现象,就必须相应提高事务的隔离级别。但是数据库的隔离级别越高,对应的并发能力就越弱,性能也就相应的越差,所以我们还需根据具体的应用场景去权衡。

三、事务隔离级别

1、未提交读

事务的最低隔离级别,在这种隔离级别下,一个事务可以读取另外一个事务未提交的数据。

数据库锁实现原理:

事务T在读数据的时候并未对数据进行加锁,事务T在修改数据的时候对数据增加行级共享锁

T1在读取数据时,T2可以对相同数据进行读取、修改。因为T1没有进行任何锁操作;当T2对记录进行修改时,T1再次读取数据可以读取到T2修改后的数据。因为T2对数据进行修改只增加了行级共享锁,T1可以再增加共享读锁进行数据读取(尽管T2没有提交事务)

如上所述,这种隔离级别,会导致脏读现象

2、已提交读

在一个事务修改数据过程中,如果事务没有进行提交,其他事务不能读取该数据

数据库锁实现原理:

事务T在读取数据时增加行级共享锁,读取一旦结束,立即释放;事务T在修改数据时增加行级排他锁,直到事务结束才释放。

T1在读取数据的过程中,T2也可以对相同数据进行读取,但是不能进行修改(T1增加的是共享锁,T2也可以增加共享锁,但是不能增加排他锁)。T1读取结束后,会立即释放共享锁,这时T2可以增加排他锁,对数据进行修改,而此时T1既不能对数据进行读取也不能进行修改,直到T2事务结束。

如上所述,这种隔离级别,解决了脏读问题,但是不能解决不可重复读现象。

3、可重复读

事务T在数据读取时,必须增加行级共享锁,直到事务结束;事务T在修改数据过程中,必须增加行级排他锁,直到数据结束。

数据库锁实现原理:

T1在读取数据的过程中,T2也可以对相同数据进行读取,但是不能进行修改(T1增加的是共享锁,T2也可以增加共享锁,但是不能增加排他锁)。直到T1事务结束后,才会释放共享锁,这时T2才可以增加排他锁,对数据进行修改。

如上所述,这种隔离级别,解决了不可重复读现象,但是这种隔离级别解决不了幻读的问题:

T1进行查询,读取了10条记录,并对十条记录增加了行级锁,此时T2是无法对这10行数据进行修改操作的,但是由于没有表级锁,它可以增加一条满足T1查询条件的记录。随后T1在进行查询时,会发现虽然10条记录没有改变,但是突然多了一条记录。

4、序列化

产生幻读是由于没有进行范围查询时没有增加范围锁。

数据库锁实现原理:

 事务T在读取数据时,必须先增加表级共享锁,直到事务结束才释放;事务T在修改数据时,必须先增加表级排他锁,直到事务结束才释放。

T1在读取A表时,增加了表级共享锁,此时T2也可以读取A表,但是不能进行任何数据的修改,直到T1事务结束。随后T2可以增加对A表的表级排他锁,此时T1不能读取A表中的任何数据,更不能进行修改。

如上所述,可序列化解决了脏读、不可重复读、幻读等读现象,但是隔离级别越来越高的同时,在并发性上也就越来越低。

四、事务操作实践 

默认情况下,MYSQL是自动提交的,也就意味着平时我们执行一条update语句时,MYSQL是自动帮我们提交的,尽快我们没有显示执行commit命令。但是这种只适用于单条SQL的执行。

如果我们想要同时执行多条SQL,并且执行过程中有SQL执行异常,需要回滚前面已经成功执行的SQL或者最终想回滚全部,则必须显示的使用事务。

  1. 开始一项事务:start tr ansaction或者begin;
  2. 提交事务:commit;
  3. 回滚事务:rollback;
  4. 事务提交之后的操作:chain;
  5. 事务回滚之后的操作:release;
  6. 修改当前连接的提交方式:set autocommit;如果设置了set autocommit=0,则设置之后所有的事务都需要显式的通过命令来进行提交或者回滚。

查询当前会话的事务隔离级别

查询当前系统的事务隔离级别

修改当前会话的事务隔离级别

提交读演示

客户端A 开启事务,并更新数据

此时事务还没有提交,开启客户端B,并进行查询,此时的数据还是未更新前的

客户端A进行事务提交,然后客户端B查询,此时是最新的数据

commit and chain的演示

如果在提交的时候使用commit and chain,那么在提交后立即开始一个新的事务

A提交事务后,B再进行查询

开启事务会隐式解锁

锁表期间,用start transaction 命令开始一个新事务,则会隐式的执行unlock tables

A对表进行写锁操作

此时B进行查询:由于被A锁表,所以查询被阻塞

A开启一个事务

由于A开启事务,隐式的释放了写锁,所以B的查询不再被阻塞

SAVEPOINT的使用

事务中可以通过定义SAVEPOINT,指定回滚事务的一个部分 A开启事务并insert一条记录,并设置savepoint

B进行查询,查询到的是开启事务前的数据

A又插入一条数据,然后回滚到savepoint

B进行查询

文章来源:http://www.cnblogs.com/dongguacai/p/7114885.html

推荐阅读:http://www.roncoo.com/article/index?title=mysql

原文链接:http://www.roncoo.com/article/detail/131740

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏散尽浮华

Mysql优化系列(0)--总结性梳理

对于一个网站来说,在运行很长一段时间后,数据库瓶颈问题会越来越暴露出来。作为运维人员,对数据库做必要的优化十分重要! 下面总结以往查阅到的以及自己工作中的一些优...

22780
来自专栏程序你好

MySQL内存溢出问题:故障排除指南

在本文中,我将向您展示如何使用新版本的MySQL(5.7+),以及如何更容易地解决 MySQL内存分配中出现的问题。

74120
来自专栏数据库

浅谈MySQL的事务隔离级别

推荐阅读 微服务: springboot系列教程学习 源码:Javaweb练手项目源码下载 调优:十五篇好文回顾 面试笔试:面试笔试整理系列 希望这篇文章能够阐...

23380
来自专栏云计算教程系列

让你的PostgreSQL更安全

PostgreSQL是自由的对象-关系型数据库服务器,在灵活的BSD风格许可证下发行。它在其他开放源代码数据库系统和专有系统之外,为用户又提供了一种选择。 我们...

26960
来自专栏容器云生态

Docker监控方案(TIG)的研究与实践之Influxdb

前言: Influxdb也是有influxdata公司(www.influxdata.com )开发的用于数据存储的时间序列数据库.可用于数据的时间排列。在整个...

33180
来自专栏云计算教程系列

如何使用Symlink更改MySQL数据目录

数据库随着时间的推移而增长,有时会超出文件系统的空间。当它们与操作系统的其余部分位于同一分区时,可能会遇到I/O冲突。RAID,网络块存储和其他设备可以提供冗余...

18860
来自专栏腾讯云数据库(TencentDB)

MySQL 8.0 版本功能变更介绍

作者介绍:朱强,腾讯云数据库高级工程师,主要负责腾讯云数据库MySQL的开发和运营,曾就职于华为和网宿,在存储、文件系统开发有丰富经验。

1.4K560
来自专栏IT技术精选文摘

MySQL高可用方案

第一种:主从复制+读写分离 客户端通过Master对数据库进行写操作,slave端进行读操作,并可进行备份。Master出现问题后,可以手动将应用切换到slav...

48880
来自专栏L宝宝聊IT

Mysql性能优化——慢查询分析

MYSQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈,CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入...

15220
来自专栏CaiRui

Zabbix监控详解

Zabbix是什么 Zabbix 是由Alexei Vladishev创建,目前由Zabbix SIA在持续开发和支持。 Zabbix 是一个企业级的分布式开源...

2.4K80

扫码关注云+社区

领取腾讯云代金券