精华内容
下载资源
问答
  • 查看事务隔离级别MySQL ,可以通过show variables like ‘%tx_isolation%’或select @@tx_isolation;语句来查看当前事务隔离级别。 查看当前事务隔离级别的 SQL 语句和运行结果如下: mysql> show variables ...
  • MySQL 四种事务隔离级别详解及对比 按照SQL:1992 事务隔离级别,InnoDB默认是可重复读的(REPEATABLE READ)。MySQL/InnoDB 提供SQL标准所描述的所有四个事务隔离级别。你可以在命令行用–transaction-isolation选项...
  • 数据库事务隔离级别 数据库事务的隔离级别有4个,由低到高依次为 Read uncommitted:允许脏读。 Read committed: 防止脏读,最常用的隔离级别,并且是大多数数据库的默认隔离级别。 Repeatable read:可以防止脏...
  • MySQL事务隔离级别详解

    万次阅读 多人点赞 2018-12-27 00:45:23
    MySQL中事务支持实在引擎层实现的,MySQL是一个支持多引擎的系统,但并不是所有引擎都支持事务。比如MySQL原生的 MyISAM 引擎就不支持事务,这也是 MyISAM 被取代的原因之一。 隔离事务的四大特性AC...

    前言

    提到事务,你肯定不会陌生,最经典的例子就是转账,甲转账给乙100块,当乙的账户中到账100块的时候,甲的账户就应该减去100块,事务可以有效的做到这一点。

    在MySQL中,事务支持实在引擎层实现的,MySQL是一个支持多引擎的系统,但并不是所有引擎都支持事务。比如MySQL原生的 MyISAM 引擎就不支持事务,这也是 MyISAM 被取代的原因之一。

    隔离性

    事务的四大特性ACID(Atomicity原子性、Consistency一致性、Isolation隔离性、Durability持久性),今天就来讲讲其中的隔离性。当数据库上有多个事务同时执行的时候,就可能出现脏读(dirty read)、不可重复读(non-repeatable read)、幻读(phantom read)的问题,为了解决这些问题,就有了事务隔离级别这个概念。

    首先你要知道,隔离得越结实,效率就会越低。因此需要我们考虑到这两点,不能只顾一边。SQL的标准事务隔离级别包括:

    • 读未提交(read uncommitted):一个事务还没有提交时,它做的变更就能被别的事务看到。
    • 读提交(read committed):一个事物提交之后,它做的变更才会被其他事务看到。
    • 可重复读(repeatable read):一个事物执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。未提交变更对其他事务也是不可见的。
    • 串行化(serializable):对于同一行记录,写会加“写锁”,读会加“读锁”,当出现锁冲突时,后访问的事务需要等前一个事务执行完成,才能继续执行。

    其中,“读提交”和“可重复读”比较难理解,这里用一个例子说明一下。

    假设数据表中 T 中只有一列,其中一行的值为 1,下面是按照时间顺序执行两个事务的行为。

    create table T(c int) engine = InnoDB;
    insert into T(c) values(1);

    在不同的隔离级别下,事务A查到的结果是不一样的:

    • 若隔离级别是“读未提交”,则 v1 的值是2,这时候事务 B 虽然还没有提交,但是结果已经被事务 A 看到了,因此 v2、v3 也都是2。
    • 若隔离级别是“读提交”,则 v1 是1,v2 的值是 2 ,事务 B 的更新在提交后才被事务 A 看到。所以 v3 的值也是 2。
    • 若隔离级别是“可重复读”,则 v1、v2是 1,v3 是2,之所以 v2 还是1,遵循的就是这个要求:事务在执行期间看到的数据前后必须是一致的。
    • 若隔离级别是“串行化”,则在事务 B 执行“将 1 改成 2”的时候,会被锁住,直到事务 A 提交后,事务 B 才可以继续执行,所以从事务 A 的角度看,v1、v2的值是1,v3的值是2。

    在实现上,数据库里面会创建一个视图,访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下,这个视图是在事务启动时创建的,整个事务存在期间都用这个视图。在“读提交”隔离级别下,这个视图是在每个SQL语句开始执行的时候创建的。这里需要注意的是,“读未提交”隔离界别下直接返回记录上的最新值,没有视图概念;而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

    我们可以看到在不同的隔离级别下,数据库行为是有所不同的。Oracle 数据库的默认隔离级别其实是“读提交”,因此对于一些从 Oracle 迁移到 MySQL 的应用,为保证数据库隔离级别的一致,你一定要记得将 MySQL 的隔离级别设置为“读提交”。配置的方式是:将启动参数 transaction-isolation 的值设置成 READ-COMMITTED,你可以用 show variables 来查看当前的值。

    每个事务隔离级别都有存在的意义,都有自己的场景,下面来一个“可重复读”的场景:

    假设你在管理一个个人银行账户表,一个表存了每个月月底的余额,一个表存了账单明细,这时候你要做数据校对,也就是判断上个月的余额和当前余额的差额,是否与本月的账单明细一致。你一定希望在校对过程中,即使有用户发生了一笔新的交易,也不影响你的校对结果。这时候使用“可重复读”隔离级别就很方便,事务启动时的视图可以认为是静态的,不受其他事务更新的影响。

    事务隔离的实现

    理解了事务隔离级别,我们再来看看事务隔离具体是怎么实现的,这里我们展开说明“可重复读”。在MySQL中,实际上每条记录在更新的时候都会同时记录一条回滚操作,记录上的最新值,通过回滚操作,都可以得到前一个状态的值。

    假设一个值从 1 被按顺序改成了 2、3、4,在回滚日志里面就会有类似下面的记录。

    当前值是 4,但是在查询这条记录的时候,不同时刻启动的事务会有不同的 read-view。如图中看到的,在试图 A、B、C里面,这一个记录的值分别是 1、2、4,同一条记录在系统中可以存在多个版本,就是数据库的多版本并发控制(MVCC)。对于 read-view A,要得到1,就必须将当前值依次执行图中所有的回滚操作得到。

    同时你会发现,即使现在有另外一个事务正在将 4 改成 5,这个事务跟 read-view A、B、C对应的事务是不会冲突的。那么回滚日志什么时候删除呢?答案是,再不需要的时候才删除,也就是说,系统会判断,当没有事务在需要用到这些回滚日志时,回滚日志才会被删除。什么时候才是不需要了呢?就是当系统里没有比这个回滚日志更早的 read-view 的时候。

    基于上面的说明,我们来讨论一下为什么建议你尽量不要使用长事务。

    长事务意味着系统里面会存在很老的事务视图,由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这就会导致大量占用存储空间。

    在MySQL 5.5 及以前的版本,回滚日志是跟数据字典一起放在 ibdata 文件里的,即使长事务最终提交,回滚段被清理,文件也不会变小,作者见过数据只有20GB,而回滚段有200GB 的库,最终只好为了清理回滚段,重新建个库。

    除了对回滚段的影响,长事务还占用锁资源,也可能拖垮整个库,这个我们会在后面讲锁的时候展开。

    事务的启动方式

    如前面所述,长事务有这些潜在风险,我当然建议读者尽量避免。其实很多业务开发的同学并不是有意使用长事务,通常是由于误用所致。MySQL 的事务启动方式有以下几种:

    1. 显式启动事务语句,begin 或者 start transaction。配套的提交语句是 commit,回滚语句是 rollback。
    2. set autocommit = 0,这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句,这个事务就启动了,而且并不会自动提交。这个事务持续存在直到你主动 commit 或者 rollback 语句,或者断开连接。

    有些客户端连接框架会默认连接成功后先执行一个 set autocommit = 0 的命令。这就导致接下来的查询都在事务中,如果是长连接,就导致了意外的长事务,因此,建议读者总是使用 set autocommit = 1,通过显式语句的方式来启动事务。

    但是有的开发同学会纠结“多一次交互”的问题。对于一个需要频繁使用事务的业务。第二种方式每个事务在开始时都不需要主动执行一次“begin”,减少了语句的交互次数。如果读者也有这个顾虑,建议使用 commit work and chain 语法。

    在 autocommit 为 1 的情况下,用 begin 显式启动的事务,如果执行 commit 则提交事务。如果执行 commit work and chain,则是提交事务并自动启动下一个事务,这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

    你可以在 information_schema 库的 innodb_trx 这个表中查询长事务,比如下面这个语句,用于查找持续时间超过 60s 的事务。

    select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

    小结

    这篇文章主要介绍了MySQL的事务隔离几倍的现象和实现,根据实现原理分析了长事务存在的风险,以及如何用正确的方式避免长事务。

    篇尾继续留个问题:现在知道了系统里面如何避免长事务,如果你是业务开发负责人同时也是数据库负责人,你会有什么方案来避免出现或者处理这种情况呢?问题答案下篇揭晓。

    上一篇问题答案

    上一篇的问题是:

    前面说到定期全量备份的周期,“取决于系统重要性,有的是一天一备,有的是一周一备”,那么在什么场景下,一天一备会比一周一备更有优势呢?或者说,它影响了这个数据库系统的哪个指标?

    优势是“最长恢复时间”更短。

    在一天一备的模式里,最坏的情况下需要应用一天的 binlog。比如,每天 0 点做一次全量备份,而要恢复出一个到昨天晚上 23 点的备份。在一周一备的模式里,最坏的情况就是需要应用一周的 binlog 了。

    数据库系统中对应的指标就是恢复目标时间(RTO)。当然,一天一备的成本要远高于一周一备,毕竟是全量备份。所以全量备份的周期还是需要根据业务重要性来评估。

    展开全文
  • 主要介绍了MySQL事务及Spring隔离级别实现原理详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
  • 作者:伞U ...好久没碰数据库了,只是想起自己当时在搞数据库的...为了说明问题,我们打开两个控制台分别进行登录来模拟两个用户(暂且成为用户 A 和用户 B 吧),并设置当前 MySQL 会话的事务隔离级别。 一. read unco
  • mysql事务隔离级别

    千次阅读 2019-06-01 08:13:07
    隔离级别 MySQL是一个服务器/客户端架构的软件,对于同一个服务器来说,可以有若干个客户端与之连接,每个客户端与服务器连接上之后,就可以称之为一个会话(Session)。我们可以同时在不同的会话里输入各种语句,...

    隔离级别

    MySQL是一个服务器/客户端架构的软件,对于同一个服务器来说,可以有若干个客户端与之连接,每个客户端与服务器连接上之后,就可以称之为一个会话(Session)。我们可以同时在不同的会话里输入各种语句,这些语句可以作为事务的一部分进行处理。不同的会话可以同时发送请求,也就是说服务器可能同时在处理多个事务,这样子就会导致不同的事务可能同时访问到相同的记录。我们前边说过事务有一个特性称之为隔离性,理论上在某个事务对某个数据进行访问时,其他事务应该进行排队,当该事务提交之后,其他事务才可以继续访问这个数据。但是这样子的话对性能影响太大,所以设计数据库的大叔提出了各种隔离级别,来最大限度的提升系统并发处理事务的能力,但是这也是以牺牲一定的隔离性来达到的。

    未提交读(READ UNCOMMITTED)脏读

    如果一个事务读到了另一个未提交事务修改过的数据,那么这种隔离级别就称之为未提交读(英文名:READ UNCOMMITTED),示意图如下:

     

     

    如上图,Session ASession B各开启了一个事务,Session B中的事务先将id1的记录的列c更新为'关羽',然后Session A中的事务再去查询这条id1的记录,那么在未提交读的隔离级别下,查询结果就是'关羽',也就是说某个事务读到了另一个未提交事务修改过的记录。但是如果Session B中的事务稍后进行了回滚,那么Session A中的事务相当于读到了一个不存在的数据,这种现象就称之为脏读,就像这个样子:

    脏读违背了现实世界的业务含义,所以这种READ UNCOMMITTED算是十分不安全的一种隔离级别

    已提交读(READ COMMITTED)不可重复读

    如果一个事务只能读到另一个已经提交的事务修改过的数据,并且其他事务每对该数据进行一次修改并提交后,该事务都能查询得到最新值,那么这种隔离级别就称之为已提交读(英文名:READ COMMITTED),如图所示:

     

    从图中可以看到,第4步时,由于Session B中的事务尚未提交,所以Session A中的事务查询得到的结果只是'刘备',而第6步时,由于Session B中的事务已经提交,所以Session B中的事务查询得到的结果就是'关羽'了。

    对于某个处在在已提交读隔离级别下的事务来说,只要其他事务修改了某个数据的值,并且之后提交了,那么该事务就会读到该数据的最新值,比方说:

     

    image_1d6urs4l0g799959e1jsj1cvqai.png-170.6kB

     

     

    我们在Session B中提交了几个隐式事务,这些事务都修改了id1的记录的列c的值,每次事务提交之后,Session A中的事务都可以查看到最新的值。这种现象也被称之为不可重复读

    可重复读(REPEATABLE READ)mysql默认的隔离级别

    在一些业务场景中,一个事务只能读到另一个已经提交的事务修改过的数据,但是第一次读过某条记录后,即使其他事务修改了该记录的值并且提交,该事务之后再读该条记录时,读到的仍是第一次读到的值,而不是每次都读到不同的数据。那么这种隔离级别就称之为可重复读(英文名:REPEATABLE READ),如图所示:

     

    image_1d6useq9aagi9981sm21b011dt4bf.png-171.1kB

     

     

    从图中可以看出来,Session A中的事务在第一次读取id1的记录时,列c的值为'刘备',之后虽然Session B中隐式提交了多个事务,每个事务都修改了这条记录,但是Session A中的事务读到的列c的值仍为'刘备',与第一次读取的值是相同的。

    串行化(SERIALIZABLE)幻读

    如果一个事务先根据某些条件查询出一些记录,之后另一个事务又向表中插入了符合这些条件的记录,原先的事务再次按照该条件查询时,能把另一个事务插入的记录也读出来,那就意味着发生了幻读,示意图如下:

     

    如上图,Session A中的事务先根据条件number > 0这个条件查询表hero,得到了name列值为'刘备'的记录;之后Session B中提交了一个隐式事务,该事务向表hero中插入了一条新记录;之后Session A中的事务再根据相同的条件number > 0查询表hero,得到的结果集中包含Session B中的事务新插入的那条记录,这种现象也被称之为幻读

    有的同学会有疑问,那如果Session B中是删除了一些符合number > 0的记录而不是插入新记录,那Session A中之后再根据number > 0的条件读取的记录变少了,这种现象算不算幻读呢?明确说一下,这种现象不属于幻读幻读强调的是一个事务按照某个相同条件多次读取记录时,后读取时读到了之前没有读到的记录

    小贴士: 那对于先前已经读到的记录,之后又读取不到这种情况,算啥呢?其实这相当于对每一条记录都发生了不可重复读的现象。幻读只是重点强调了读取到了之前读取没有获取到的记录。

     

    展开全文
  • 这里通过分析MySQL中InnoDB引擎的加锁机制,来抛砖引玉,让读者更好的理解,在事务处理数据库到底做了什么。  #一次封锁or两段锁?  因为有大量的并发访问,为了预防死锁,一般应用推荐使用一次封锁法,是在...
  • 数据库隔离级别有四种,应用《高性能mysql》一书的说明:然后说说修改事务隔离级别的方法:1.全局修改,修改mysql.ini配置文件,在最后加上#可选参数有:READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,...
  • 之前在网上查询mysql事务隔离相关资料,一直只是脑子里有一个印象,久而久之还是会忘记,忘记后又要到网上查找相关资料,但是没实践过就对mysql事务隔离级别理解不是特别的深入,现在自己亲手实践体验一下这个这四个...
  • MySQL-事务隔离级别设置

    千次阅读 2019-02-20 19:36:59
    撤销一个事务时, 把其他事务已经提交的更新数据覆盖(此情况在事务中不可能出现, 因为一个事务中修改时此记录已加锁, 必须等待此事务完成后另一个事务才可以继续UPDATE) 脏读 一个事务读到另一个事务,尚未提交的...

    先了解下 第一类丢失更新、脏读、不可重复读、幻读、第二类丢失更新

    第一类丢失更新

    撤销一个事务时, 把其他事务已经提交的更新数据覆盖(此情况在事务中不可能出现, 因为一个事务中修改时此记录已加锁, 必须等待此事务完成后另一个事务才可以继续UPDATE)

    脏读

    一个事务读到另一个事务,尚未提交的修改,就是脏读。这里所谓的修改,除了Update操作,不要忘了,还包括Insert和Delete操作。脏读的后果:如果后一个事务回滚,那么它所做的修改,统统都会被撤销。前一个事务读到的数据,就是垃圾数据。

    举个例子:预订房间。

    有一张Reservation表,往表中插入一条记录,来订购一个房间。

    事务1:在Reservation表中插入一条记录,用于预订99号房间。

    事务2:查询,尚未预定的房间列表,因为99号房间,已经被事务1预订。所以不在列表中。

    事务1:信用卡付款。由于付款失败,导致整个事务回滚。 所以插入到Reservation 表中的记录并不置为持久(即它将被删除)。

    现在99号房间则为可用。

    所以,事务2所用的是一个无效的房间列表,因为99号房间,已经可用。如果它是最后一个没有被预定的房间,那么这将是一个严重的失误。

    注:脏读的后果很严重。

    再例如

    时间取款事务支票转账事务
    T1开始事务 
    T2 开始事务
    T3查询账户余额为1000元 
    T4  
    T5取出100,把存款余额改为900元 
    T6 查询账户的存款余额为900元(脏读)
    T7撤销该事务,把存款余额恢复为1000元 
    T8 汇入100元,把存款余额改为1000元
    T9 提交事务

     

    不可重复读

    在同一个事务中,再次读取数据时【就是你的select操作】,所读取的数据,和第1次读取的数据,不一样了。就是不可重复读。

    举个例子:

    事务1:查询有双人床房间。99号房间,有双人床。

    事务2:将99号房间,改成单人床房间。

    事务1:再次执行查询,请求所有双人床房间列表,99号房间不再列表中了。也就是说,事务1,可以看到其他事务所做的修改。

    在不可重复读,里面,可以看到其他事务所做的修改,而导致2次的查询结果不再一样了。这里的修改,是提交过的。也可以是没有提交的,这种情况同时也是脏读。

    如果,数据库系统的隔离级别。允许,不可重复读。那么你启动一个事务,并做一个select查询操作。查询到的数据,就有可能,和你第2次,3次...n次,查询到的数据不一样。一般情况下,你只会做一次,select
    查询,并以这一次的查询数据,作为后续计算的基础。因为允许出现,不可重复读。那么任何时候,查询到的数据,都有可能被其他事务更新,查询的结果将是不确定的。

    注:如果允许,不可重复读,你的查询结果,将是不确定的。一个不确定的结果,你能容忍吗?

    幻读

    事务1读取指定的where子句所返回的一些行。然后,事务2插入一个新行,这个新行也满足事务1使用的查询where子句。然后事务1再次使用相同的查询读取行,但是现在它看到了事务2刚插入的行。这个行被称为幻象,因为对事务1来说,这一行的出现是不可思议的。

    举个例子:

    事务1:请求没有预定的,双人床房间列表。

    事务2:向Reservation表中插入一个新纪录,以预订99号房间,并提交。

    事务1:再次请求有双人床的未预定的房间列表,99号房间,不再位于列表中。

    注:幻读,针对的是,Insert操作。如果事务2,插入的记录,没有提交。那么同时也是脏读。

    第二类丢失更新

    这是不可重复读中的特例, 一个事务覆盖另一个事务已提交的更新数据(以下会出现此情况)

    时间取款事务支票转账事务
    T1开始事务 
    T2 开始事务
    T3查询账户余额为1000元 
    T4 查询账户余额为1000元
    T5取出100,把存款余额改为900元 
    T6提交事务 
    T7 汇入100元,把存款余额改为1100元
    T8 提交事务

     

     

    No事务1事务2备注
    1select * from tb2 where id=1;
    <NO> id name name2
    1 1 a a
      
    2 update tb2 set name2='b' where id=1;
    1 Rows updated!
     
    3select * from tb2 where id=1;
    <NO> id name name2
    1 1 a a
     很好:没有出现脏读
    4 commit 
    5select * from tb2 where id=1;
    <NO> id name name2
    1 1 a a
     很好:没有出现不可重复读
    6update tb2 set name='a1' where id=1;
    1 Rows updated!
      
    7select * from tb2 where id=1;
    <NO> id name name2
    1 1 a1 b
     糟糕:name2的值怎么跟以前不一样了。(即出现了不可重复读)
    8commit  

     

    应该明白 什么状况对什么加什么锁(可以分别思考和测试每个隔离级别下脏读、不可重复读、幻读的出现及不出现原因)

    读完下面的内容可以回头来看此段:

      不可重复读错误处理:通常可以用 set tran isolation level repeatable read 来设置隔离级别, 这样事务 A 在两次读取表T中的数据时, 事务B如果企图更改表T中的数据(细节到事务A读取数据)时, 就会被阻塞, 直到事务A提交! 这样就保证了, 事务A两次读取的数据的一致性

      幻读错误处理:如果设置 repeatable read, 虽然可以防止事务B对数据进行修改, 但是事务B却可以向表T中插入新的数据, 如何防止这个问题, 我们可以考虑设置最高的事务隔离级别 set tran isolation level serializable, 于是乎, 事务B就只能乖乖的等待事务A的提交, 才能向表T中插入新的数据, 从而避免了幻读

     

    不可重复读与幻读的区别:

    脏读的重点是读另一个事务未提交的数据(假若那个事务RollBack, 则这数据就是无效的):某个事务已更新一份数据, 另一个事务在此时读取了同一份数据, 由于某些原因, 前一个RollBack了操作, 则后一个事务所读取的数据就会是不正确的

    不可重复读的重点是修改: 同样的条件, 你读取过的数据, 再次读取出来发现值不一样了

    幻读的重点在于新增或者删除: 同样的条件, 第1次和第2次读出来的记录数不一样

    当然, 从总的结果来看, 似乎两者都表现为两次读取的结果不一致, 但如果你从控制的角度来看, 两者的区别就比较大, 对于不可重复读, 只需要锁住满足条件的记录, 对于幻读, 要锁住满足条件及其相近的记录

     

    rollback;或者commit;都会退出事务!

    更新记录的时候需要锁!

     

    InnoDB中 只要此行在别的事务中产生了锁 本事务中就不允许进行修改 只能阻塞 等待别的事务提交

     

    一般设置为Read Committed, 并且利用悲观锁(SELECT ... FOR UPDATE)或乐观锁(SET NUM=NUM+1, 而不是计算后再插入, 避免并发)来处理特殊情况

     

    SQL标准定义了4类隔离级别,包括了一些具体规则,用来限定事务内外的哪些改变是可见的,哪些是不可见的。低级别的隔离级一般支持更高的并发处理,并拥有更低的系统开销。
    Read Uncommitted(读取未提交内容)

    在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少。读取未提交的数据,也被称之为脏读(Dirty Read)。
    Read Committed(读取提交内容)

    这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)。它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读(Nonrepeatable Read),因为同一事务的其他实例在该实例处理其间可能会有新的commit,所以同一select可能返回不同结果。
    Repeatable Read(可重读)

    这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。不过理论上,这会导致另一个棘手的问题:幻读 (Phantom Read)。简单的说,幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题。

    Serializable(可串行化)
    这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。在这个级别,可能导致大量的超时现象和锁竞争。只要操作产生了锁,就不允许其他事务读取和修改!(可以看看加锁处理分析http://hedengcheng.com/?p=771或者百度网盘http://pan.baidu.com/s/1mgN00Og)

    这四种隔离级别采取不同的锁类型来实现,若读取的是同一个数据的话,就容易发生问题。例如:

    脏读(Drity Read):某个事务已更新一份数据,另一个事务在此时读取了同一份数据,由于某些原因,前一个RollBack了操作,则后一个事务所读取的数据就会是不正确的。

    不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致,这可能是两次查询过程中间插入了一个事务更新的原有的数据。

    幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致,例如有一个事务查询了几列(Row)数据,而另一个事务却在此时插入了新的几列数据,先前的事务在接下来的查询中,就会发现有几列数据是它先前所没有的。

    在MySQL中,实现了这四种隔离级别,分别有可能产生问题如下所示:

    隔离级别是否出现第一类丢失更新是否出现脏读是否出现不可重复读是否出现幻读是否出现第二类丢失更新
    Serializable
    Repeatable Read
    Read Commited
    Read Uncommited

     

    下面,将利用MySQL的客户端程序,分别测试几种隔离级别。测试数据库为test,表为tx;表结构:

    idint

    num

    int

    两个命令行客户端分别为A,B;不断改变A的隔离级别,在B端修改数据。

     

    SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

     

     

    (一)、将A的隔离级别设置为read uncommitted(未提交读)

    在B未更新数据之前:

    客户端A:

    B更新数据:

    客户端B:

    客户端A:

    经过上面的实验可以得出结论,事务B更新了一条记录,但是没有提交,此时事务A可以查询出未提交记录。造成脏读现象。未提交读是最低的隔离级别。

    (二)、将客户端A的事务隔离级别设置为read committed(已提交读)

    在B未更新数据之前:

    客户端A:

    B更新数据:

    客户端B:

    客户端A:

    经过上面的实验可以得出结论,已提交读隔离级别解决了脏读的问题,但是出现了不可重复读的问题,即事务A在两次查询的数据不一致,因为在两次查询之间事务B更新了一条数据。已提交读只允许读取已提交的记录,但不要求可重复读。

    (三)、将A的隔离级别设置为repeatable read(可重复读)

    在B未更新数据之前:

    客户端A:

    B更新数据:

    客户端B:

    客户端A:

    B插入数据:

    客户端B:

    客户端A:

    由以上的实验可以得出结论,可重复读隔离级别只允许读取已提交记录,而且在一个事务两次读取一个记录期间,其他事务部的更新该记录。但该事务不要求与其他 事务可串行化。例如,当一个事务可以找到由一个已提交事务更新的记录,但是可能产生幻读问题(注意是可能,因为数据库对隔离级别的实现有所差别)。像以上 的实验,就没有出现数据幻读的问题。

    (四)、将A的隔离级别设置为 可串行化 (Serializable)

    A端打开事务,B端插入一条记录

    事务A端:

    事务B端:

    因为此时事务A的隔离级别设置为serializable,开始事务后,并没有提交,所以事务B只能等待。

    事务A提交事务:

    事务A端

    事务B端

     

    serializable完全锁定字段,若一个事务来查询同一份数据就必须等待,直到前一个事务完成并解除锁定为止 。是完整的隔离级别,会锁定对应的数据表格,因而会有效率的问题。

     

    利用悲观锁

     

     

    时间取款事务支票转账事务
    T1开始事务 
    T2 开始事务
    T3select * from ACCOUNTS where ID = 1 for update;
    查询账户余额为1000元;这条记录被锁定。
     
    T4 select * from ACCOUNTS where ID = 1 for update;
    执行该语句时,事务停下来等待取款事务解除对这条记录的锁定。
    T5取出100,把存款余额改为900元 
    T6提交事务 
    T7 事务恢复运行,查询结果显示存款余额为900.这条记录被锁定。
    T8 汇入100元,把存款余额改为1000元。
    T9 提交事务

     

    用图展示隔离级别及对应缺陷:

      READ UNCOMMITTED 在该隔离级别下,所有事务都可以看到其它未提交事务的执行结果。如下图所示:

        缺陷:事务2查询到的数据是事务1中修改但未提交的数据,但因为事务1回滚了数据,所以事务2查询的数据是不正确的,因此出现了脏读的问题。

      READ COMMITTED 在该隔离级别下,一个事务从开始到提交之前对数据所做的改变对其它事务是不可见的,这样就解决在READ-UNCOMMITTED级别下的脏读问题。但如果一个事务在执行过程中,其它事务的提交对该事物中的数据发生改变,那么该事务中的一个查询语句在两次执行过程中会返回不一样的结果。如下图所示:


        缺陷:事务2执行update语句但未提交前,事务1的前两个select操作返回结果是相同的。但事务2执行commit操作后,事务1的第三个select操作就读取到事务2对数据的改变,导致与前两次select操作返回不同的数据,因此出现了不可重复读的问题。

      REPEATABLE READ 这是MySQL的默认事务隔离级别,能确保事务在并发读取数据时会看到同样的数据行,解决了READ-COMMITTED隔离级别下的不可重复读问题。mysql的InnoDB存储引擎通过多版本并发控制(Multi_Version Concurrency Control, MVCC)机制来解决该问题。在该机制下,事务每开启一个实例,都会分配一个版本号给它,如果读取的数据行正在被其它事务执行DELETE或UPDATE操作(即该行上有排他锁),这时该事物的读取操作不会等待行上的锁释放,而是根据版本号去读取行的快照数据(记录在undo log中),这样,事务中的查询操作返回的都是同一版本下的数据,解决了不可重复读问题。其原理如下图所示:

        缺陷:虽然该隔离级别下解决了不可重复读问题,但理论上会导致另一个问题:幻读(Phantom Read)。正如上面所讲,一个事务在执行过程中,另一个事物对已有数据行的更改,MVCC机制可保障该事物读取到的原有数据行的内容相同,但并不能阻止另一个事务插入新的数据行,这就会导致该事物中凭空多出数据行,像出现了幻读一样,这便是幻读问题。如下图所示:

        缺陷:事务2对id=1的行内容进行了修改并且执行了commit操作,事务1中的第二个select操作在MVCC机制的作用下返回的仍是v=1的数据。但事务3执行了insert操作,事务1第三次执行select操作时便返回了id=2的数据行,与前两次的select操作返回的值不一样。需要说明的是,REPEATABLE-READ隔离级别下的幻读问题是SQL标准定义下理论上会导致的问题,MySQL的InnoDB存储引擎在该隔离级别下,采用了Next-Key Locking锁机制避免了幻读问题。Next-Key Locking锁机制将在后面的锁章节中讲到。

      SERIALIZABLE 这是事务的最高隔离级别,通过强制事务排序,使之不可能相互冲突,就是在每个读的数据行加上共享锁来实现。在该隔离级别下,可以解决前面出现的脏读、不可重复读和幻读问题,但也会导致大量的超时和锁竞争现象,一般不推荐使用。

      转自:http://xm-king.iteye.com/blog/770721

      参考:http://blog.csdn.net/u013235478/article/details/50625602

    展开全文
  • 查询:默认事务隔离级别 mysql> select @@tx_isolation;当前会话的默认事务隔离级别 mysql> select @@session.tx_isolation;当前会话的默认事务隔离级别 mysql> select @@global.tx_isolation;全局的事务隔离级别
  • 主要介绍了MySql四种隔离级别,帮助大家更好的理解和学习MySQL,感兴趣的朋友可以了解下
  • 查询mysql事务隔离级别

    千次阅读 2019-10-11 16:37:59
    3、事务隔离级别越高,越能保证数据的完整性,但是对并发性能影响大,就比如隔离级别为串行化时,读取数据会锁住整张表。 4、不可重复读和幻读很相似,两者整体上都是两次读的数据不相同,但是不可重复读一般时更新...
    转载链接: https://blog.csdn.net/zhuoya_/article/details/80299250

    点击>>MySQL下的存储引擎查看MySQL下的几种存储引擎。在几种存储引擎中,可以看到只有InnoDB存储引擎支持事务,那么本篇就来介绍InnoDB下支持的事务。

    本篇目录

    **************************************************************

    *     事务是什么?                                                                   *

    *     事务遵循的四个特性  (AICD)                                        *

    *     事务中的基本操作                                                            *

    *     事务的四中隔离级别                                                         *

    *     通过设置隔离级别来保证事务的隔离性基本操作                *

    **************************************************************

    一、事务(transaction)

    事务指的就是一种SQL语句的集合。

    二、事务的四个特性


    事务的原子性通过Undo log来保证。(Undo日志记录数据修改前的状态)

    事务的持久性通过Redo log来保证。(Redo日志记录某数据块被修改后的值)

    事务的隔离性通过设置隔离级别来保证。

    事务的一直性通过Redo log来保证。

    三、事务中的基本操作

    (1)回滚:撤销指定SQL语句的功能。

    mysql>rollback;

    (2)提交:将未存储的SQL语句结果写到数据库表中。

    mysql>commit;

    (3)保留点:事务处理中设置的临时占位符。

    mysql>savepoint   XXX;

    注意点:

    1、不能回退select、create和drop语句。

    2、事务提交是默认提交。每执行一条语句就把该语句当成一个事务进行提交。

    3、当出现start transaction;语句时,隐式提交会关闭。

    4、当rollback或者commit语句出现,事务自动关闭,隐式提交自动恢复。

    5、通过设置autocommit=0可以取消自动提交,直到看到autocommit=1才会提交。

    6、如果没有设置保留点,rollback会回滚到start transaction处;如果设置了,并且在rollback指定了该保留点,则回滚到保留点(rollback to XXX)。

    四、事务的四中隔离级别

    在MySQL中有四中隔离级别。每一种隔离级别的作用也不同,下面通过一张表来看一下:

    事务的隔离级别 /  问题脏读不可重复读幻读
    未提交读(read uncommitted)没有避免没有避免没有避免
    已提交读(read committed)已避免没有避免没有避免
    可重复读(repeatable read)已避免没有避免没有避免
    可串行化/可序列化(serializable)已避免已避免

    已避免

    在MySQL中默认的隔离级别是可重复读


    五、设置隔离级来保证事务的隔离性

    该部分结合并发中出现的脏读、不可重复读和幻读来操作。

    (1)

    什么是脏读?


    案例一:未提交隔离级别无法避免脏读。

    a.客户端A开启事务,查询账户余额。


    b.在事务A未提交之前,开启客户端B,更新LIMing的账户余额。


    c.在事务B未提交时,在客户端A查询表中数据,看到了事务B更新的数据。


    d.此时,如果事务B因某些原因需要进行回滚,则所有操作被撤销,A中读到的数据就是脏数据。


    二、

    什么是不可重复读?


    案例二:已提交读避免了脏读,无法避免不可重复读。

    a.客户端A开启事务,查询表中数据。


    b.在事务A还没有提交,开启事务B,更新表中ShangGuan的账户余额。


    c.在事务A中查看ShangGuan的账户余额。余额未该变,说明杜绝了脏读。


    d.事务B提交。


    在事务A中查询账户余额,发生该变。对于开发者来说保证了数据的一致性。但是对于用户来说,两次的读取结果不一样,发生了不可重复读。


    三、

    案例三:可重复读隔离级别避免了不可重复读,但是数据任然保持一致性。

    a.设置隔离级别为可重复读,开启事务A,查询最初的账户余额。


    b.在事务A未提交时,开启事务B,更新表中LiHong的账户余额。


    c.事务B未提交时,在事务A中查询表。发现没有发生变化,避免了不可重复读。


    d.紧接着,在事务A中对LiHong的账户余额做计算,会发现把在事务B中的结果拿来用,在开发人员看来保证了事务的一致性。


    四、

    什么是幻读?


    案例四、可重复读隔离级别下的幻读

    a.在客户端A中开启事务,查询表中数据。


    b.A事务为提交,在客户端B中开启事务,向表中插入一条数据。


    c.在客户端A中计算表中账户余额总和。


    经过操作发现在RPEATABLE-READ隔离级别下并没有发生幻读现象?????

    问号脸,什么原因。

    我查了一下表的存储引擎,如下,是InnoDB存储引擎:


    再查一下,数据库存储引擎


    我忘记了在Linux下的MySQL存储引擎默认是MyISAM存储引擎,它对于非索引项会加表锁。所以查询没出现幻读可能就是这个原因吧。

    在网上查了一下,设置默认存储引擎为InnoDB步骤有:在/etc/my.cnf中,在mysqld后面增加default-storage-engine=INNODB即可。在这儿偷个懒就不验证了。

    五、

    可串行化隔离级别:

    a.在客户端A下设置隔离级别,开启事务。


    b.在A事务未提交时,开启B事务,设置隔离级别,进行插入数据,发现不允许


    ************************************************************************************************************

    总结:

    1、以上操作版本是:

    2、事务的隔离性可通过设置隔离级别来保证。

    3、事务隔离级别越高,越能保证数据的完整性,但是对并发性能影响大,就比如隔离级别为串行化时,读取数据会锁住整张表。

    4、不可重复读和幻读很相似,两者整体上都是两次读的数据不相同,但是不可重复读一般时更新引起的;幻读是插入和删除引起的。

    ***************************************************************************************************************

    展开全文
  • 主要介绍了Mysql事务隔离级别原理实例解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
  • SQL标准定义了4种隔离级别,包括了一些具体规则,用来限定事务内外的哪些改变是可见的,哪些是不可见的。下面这篇文章通过实例详细的给大家分析了关于MySQL中的四种事务隔离级别的相关资料,需要的朋友可以参考下。
  • 在学习的过程看到了mysql5的查看会话隔离级别的命令是 select @@session.tx_...会话的默认的隔离级别如下,也是MySQL中默认的事务隔离级别 mysql> select @@transaction_isolation; +----------------------
  • 主要介绍了MySQL中Innodb的事务隔离级别和锁的关系讲解教程,来自于美团技术团队的经验实际经验分享,需要的朋友可以参考下
  • MySQL设置事务隔离级别

    千次阅读 2019-02-26 20:21:58
    转自:...   修改事务隔离级别的方法: 1.全局修改,修改mysql.ini配置文件,在最后加上 1 #可选参数有:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE. 2 [mysqld]...
  • MySQL事务隔离级别

    2021-01-19 21:54:09
    MySQL事务隔离级别 数据库事务简单介绍 MySQL在执行sql语句的时候,会遇到一些场景,一系列操作必须全部执行,而不能仅执行一部分。例如,一个转账操作: -- 从id=1的账户给id=2的账户转账100元 -- 第一步:将id=1...
  • 更改MySQL的默认事务隔离级别

    万次阅读 2019-06-01 10:04:07
    MySQL事务隔离级别一共有四个: 大多数的数据库系统的默认事务隔离级别都是:Read committed 而MySQL的默认事务隔离级别...我们可以采用下面这种方法查询Mysql中的当前事务隔离级别: 然后使用命令:SET ...
  • MySQL事务隔离级别

    2019-03-21 01:54:28
    NULL 博文链接:https://cuishuangjia.iteye.com/blog/964885
  • mysql默认事务隔离级别是什么?

    千次阅读 2020-02-25 13:28:38
    mysql数据库事务隔离级别有4个,而默认的事务处理级别就是'REPEATABLE-READ',也就是可重复读。下面本篇文章就来带大家了解一下mysql的这4种事务隔离级别,希望对大家有所帮助。 SQL标准定义了4类隔离级别...
  • 然后说说修改事务隔离级别的方法: 1.全局修改,修改mysql.ini配置文件,在最后加上 代码如下: #可选参数有:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE. [mysqld] transaction-...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 90,742
精华内容 36,296
关键字:

mysql中事务的隔离级别

mysql 订阅