注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

MySQL InnoDB事务隔离级别(三)

MySQL 彭东稳 3811次浏览 已收录 3个评论

一、事务的隔离级别

通过上面事务引发的数据不一致性,来说说事务的隔离级别。隔离性其实比想象的要复杂,简单来说,隔离性的作用就是保证在多个事务并行执行时,相互之间不会影响;比如一个事务的修改对其他事务是不可见的,好似多个事务是串行执行的。

SQL 92标准定义了四个隔离级别。分别是READ UNCOMMITTEDREAD COMMITTEDREPEATABLE READSERIALIZABLE。但是很少有数据库厂商遵循这些标准,比如Oracle数据库就不支持READ UNCOMMITTEDREPEATABLE READ隔离级别。而MySQL支持这全部4种隔离级别。每一种级别都规定了一个事务中所做的修改对其他事务的所见程度。为了尽可能减少事务间的影响,事务隔离级别越高安全性越好但是并发就越差;事务隔离级别越低,事务请求的锁越少,或者保持锁的时间就越短,这也就是为什么绝大多数数据库系统默认的事务隔离级别是READ COMMITTED。MySQL是支持REPEATABLE READ隔离级别,在这个级别下可以解决“不可重复读”的问题,是真正满足ACID中隔离性的要求的,但锁粒度比READ COMMITTED隔离级别要重。在READ COMMITTED隔离级别下是无法满足隔离性要求的,所以MySQL默认是REPEATABLE READ隔离级别。

注意:做以下实验时需要关闭二进制日志功能或者将二进制日志记录格式设置为ROW,不然会有一些你不了解的错误。下面我们从低级别往高级别介绍,级别越低数据一致性越好但是性能越差,而级别越高性能越好但问题越多。希望能说明白每个级别真正的含义。

  • SERIALIZABLE(串行化)

在这个级别,它通过强制事务串行执行,避免了前面说的一系列问题。简单来说,SERIALIZABLE会在读取的每一行数据上都加排他锁(X锁),所以可能导致大量的超时和锁争用的问题。实际应用中也很少在本地事务中使用SERIALIABLE隔离级别,主要应用在InnoDB存储引擎的分布式事务中。

实例

事务A: 更新表,但不提交

事务B: 无法读取数据

事务A: 提交数据

事务B: 可以读取事务A更新后的数据

SERIALIZABLE事务隔离级别最严厉,在进行查询时就会对表或行加上共享锁,其他事务对该表将只能进行读操作,而不能进行写操作。一旦有事务对表进行了写操作,其他事务连读都不能操作。

  • REPEATABLE-READ(可重复读)

可重复读级别解决了“不可重复读”及“幻读”问题,MySQL默认使用这个级别。就是在同一个事务中发出同一个SELECT语句两次或更多次,那么产生的结果数据集总是相同的。因此,使用可重复读隔离级别的事务可以多次检索同一行集,并对它们执行任意操作,直到提交或回滚操作终止该事务。

基于读写锁,可重复读只能解决读读的并行执行,并不能使读写、写写、写读的并行执行(不明白就去事务原理再看一遍)。注意这是说在读写锁的情况下,但是现在RDBMS都使用MVCC机制了,所以会有所不同。在RR隔离级别,针对当前读(还有快照读),保证对读取到的记录加锁(行锁),同时保证对读取的范围加锁,也就是说新的满足查询条件的记录不能够插入 (由间隙锁保证),所以解决了“不可重复读”及“幻读”现象。

对于“不可重复读”及“幻读”问题,有时候很容易搞混淆。但从定义上来看,“不可重复读”主要针对update操作,对上一次读到的数据再次读取时发生了改变;而“幻读”主要针对insert及delete操作,对上一次读取到的数据条数变多或变少了。

实例

事务A: 修改表,id=1的记录,不提交事务

事务B: 查看表

事务A: 提交数据

事务A: 读取数据,发现就算事务A提交了数据,事务B也不会读取到事务更新的新数据

PS: RR级别使用了MVCC机制,每次读取数据即为事务开始时数据的快照版本。

  • READ-COMMITTED(读已提交的数据)

在这个级别,能满足前面提到的隔离性的简单定义:一个事务开始时,只能“看见”已经提交的事务所做的修改。换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。这个级别有时候也叫“不可重复读(non-repeatable read)”,因为两次执行同样的查询,可能会得到不一样的结果。为什么会出现“不可重复读”问题呢?从下面的分析中找答案。

RC隔离级别,针对当前读(还有快照读)保证对读取到的记录加锁 (记录锁)。因为在RC的事务隔离级别下,除了唯一性的约束检查和外键约束的检查需要Gap Lock外,InnoDB存储引擎不会使用Gap Lock,所以会产生“不可重复读”问题。而不可重复读会破坏事务隔离性要求,也就是一个事务的修改操作对其他事务可见了。

实例

事务A: 修改表,id=1的记录,不提交事务

事务B: 查看表,并没有发生变化

事务A: 提交事务

事务B: 查看数据,id=1的记录已经发生变化

PSRC级别使用了MVCC机制,即发生并行写读时,那么数据读取时就会使用快照读而不是当前读。

另外,使用这个事务隔离级别需要注意的一些问题,如二进制日志之只能够使用ROW(或MIXED)格式,如果使用STATEMENT会导致主从不一致的问题。接下来,通过一个实例看看RC隔离级别下为什么要用ROW格式(二进制日志要开启)?

根据上述实验证明,如果当你隔离级别为RC,二进制格式为STATEMENT时,插入数据就会报错。为什么呢?那是因为RC隔离级别会导致“不可重复读”和“幻读”的问题,也就是说可以读取到另一个事务提交的数据。其中“幻读”问题,如果主从复制是基于STATEMENT格式的话,会导致主从数据不一致的情况,因为STATEMENT是基于SQL语句的复制模式,如下实验。

Master主机上的操作如下:

事务A:删除id<=5的数据

事务B:插入id=3的数据

事务A:提交事务

但是在slave上看到的结果是:

最后我们发现在Mastermoney表中会还有一条事务2插入的数据并没有被后提交的事务所删除,这里先说一下由于在READ COMMITTED隔离级别下,事务没有使用gap lock进行锁定,因此用户在事务2中可以在小于等于5的范围内插入一条记录。STATEMENT格式记录的是Master上产生的SQL语句,因此在Master服务器上执行的顺序为先删后插,但是在SlaveSTATEMENT格式记录的话执行时的顺序确是先插后删,逻辑顺序上产生了不一致,所以Mastermoney表中还有一条数据,而Slavemoney表中却不会有数据。解决这个主从不一致的问题即使用RR隔离级别即可,因为RR级别使用gap lock,不会允许事务2插入数据的。

  • READ-UNCOMMITTED(读尚未提交的数据)

在这个级别,只加写锁,读不加锁。那么就会产生这三种情况:读读可以并行、读写可以并行、写读可以并行(只有写写不可以并行)。所以在这个级别,一个事务的修改,即使没有提交,对其他事务也都是可见的。一个事务可以读取另一个事务未提交的数据,这也被称为“脏读(dirty read)”,同样破坏了事务隔离性要求,一个事务的修改对其他事务可见了。这个级别会导致很多问题,如一个事务可以读取到另一个事务的中间状态,且从性能上来说,READ UNCOMMITTED不会比其他的级别好太多,但却缺乏其他级别的很多好处,除非真的有非常必要的理由,在实际应用中一般很少使用。

实例

建立表

事务A:更新一条记录

事务B:查看数据以及发生变化,此时事务A还没有提交数据

事务A:进行事务回滚

事务B查看数据已经跟上一次查看不一样了,这就是读“脏”数据

PS:由此发现在RU级别会产生读“脏”数据。 

二、总结

各个级别存在的问题:

串行级别(SERIALIZABLE

锁粒度:读写全部加写锁

优缺点:数据安全性高、事务执行效率慢

可重复读级别(REPEATABLE READ

锁粒度:读加锁、写加锁

优缺点:读读可并行且同一事务内数据可重复读

读已提交(READ COMMITTED

锁粒度:读加锁、写加锁、但读锁可升级为写锁

优缺点:读读、读写可并行,但会出现不可重复读问题

读未提交(READ UNCOMMITTED

锁粒度:写加锁、读不加锁

优缺点:效率高、数据安全性不高且会出现脏读

各个具体数据库并不一定完全实现了上述4个隔离级别,例如,Oracle只提供READ COMMITTEDSERIALIZABLE两个标准隔离级别,另外还提供自己定义的Read only隔离级别;SQL Server除支持上述ISO/ANSI SQL92定义的4个隔离级别外,还支持一个叫做“快照读”的隔离级别,但严格来说它是一个用MVCC机制实现的SERIALIZABLE隔离级别。使用MVCC这种机制的如Google spanner、淘宝TDDLMySQL支持全部4个隔离级别,其默认级别为Repeatable read,但在具体实现时,有一些特点,比如在一些隔离级别下是采用MVCC一致性读。国产数据库DM也支持所有级别,其默认级别为READ COMMITTED。最后好像又有人提出了“无锁”编程的概念,真是学无止尽啊,妈了鸡,学的累死了。


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (1)or分享 (0)
关于作者:

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

(3)个小伙伴在吐槽
  1. 对呀,隔离级别为 SERIALIZABLE(串行化)时 select 一条数据 时到底加的什么锁(写锁,还是读锁)???
    2064273908@qq.com2018-09-20 15:01 (3天前) Windows 7 | Chrome 68.0.3440.106
  2. 博主大人,隔离级别为 SERIALIZABLE(串行化)时 select 一条数据 时到底加的什么锁(写锁,还是读锁),读可以并行吗
    2064273908@qq.com2018-08-15 16:00 Windows 7 | Chrome 68.0.3440.84
    • 加读锁,读锁与读锁自然可以并发
      彭东稳2018-09-21 11:24 (2天前) 未知操作系统 | Chrome 68.0.3440.106