数据库事务隔离等级解读

事务的隔离级别

为了解决这些问题，数据库管理软件采用了一些·办法设置了事务的隔离等级。隔离等级越高，数据越安全，但性能越低。

 MySQL查看当前数据库的隔离级别命令是： 

　　mysql> SELECT @@tx_isolation; 
更改隔离级别（开启事务之前更改）： 
　　mysql> SET TRANSACTION ISOLATION LEVEL [level_name]

 

客户端1：

BEGIN;
SELECT score FROM a_table;
UPDATE a_table SET score = score - 3 WHERE a_id= 2;
SELECT SLEEP(20);
COMMIT;

客户端2：

BEGIN;
SELECT score FROM a_table;
UPDATE a_table SET score = score - 2 WHERE a_id= 2;
COMMIT;

客户端1先执行，然后客户端2在执行。客户端2会等待客户端1执行完毕后在再行，结果正确。

 

 为什么会这样呢？按道理不应该呀？

这是因为a_id上没有加索引，所以update会锁表，如果加了索引，就会锁行

https://blog.csdn.net/weixin_40255793/article/details/79735665

参考文献：

[1]　[MySQL]对于事务并发处理带来的问题，脏读、不可重复读、幻读的理解 
[2]　《MySQL必知必会》

 

Innodb中的MVVC

 
 

MVVC

  1. MySQL的大多数事务型存储引擎实现的其实都不是简单的行级锁。基于提升并发性能的考虑, 它们一般都同时实现了多版本并发控制(MVCC)。不仅是MySQL, 包括Oracle,PostgreSQL等其他数据库系统也都实现了MVCC, 但各自的实现机制不尽相同, 因为MVCC没有一个统一的实现标准。
  2. 可以认为MVCC是行级锁的一个变种, 但是它在很多情况下避免了加锁操作, 因此开销更低。虽然实现机制有所不同, 但大都实现了非阻塞的读操作，写操作也只锁定必要的行。
  3. MVCC的实现方式有多种, 典型的有乐观(optimistic)并发控制 和 悲观(pessimistic)并发控制。
  4. MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

read view和快照snapshot

  1. 事务快照是用来存储数据库的事务运行情况。一个事务快照的创建过程可以概括为：
    • 查看当前所有的未提交并活跃的事务，存储在数组中
    • 选取未提交并活跃的事务中最小的XID，记录在快照的xmin中
    • 选取所有已提交事务中最大的XID，加1后记录在xmax中
  2. read view 主要是用来做可见性判断的
  3. 对于read view快照的生成时机, 也非常关键, 正是因为生成时机的不同, 造成了RC,RR两种隔离级别的不同可见性;
    1. 在innodb中(默认repeatable read级别), 事务在begin/start transaction之后的第一条select读操作后, 会创建一个快照(read view), 将当前系统中活跃的其他事务记录记录起来;
    2. 在innodb中(默认repeatable committed级别), 事务中每条select语句都会创建一个快照(read view);

相关概念

  1. undo-log
    undo log是为回滚而用，具体内容就是copy事务前的数据库内容（行）到undo buffer，在适合的时间把undo buffer中的内容刷新到磁盘。undo buffer与redo buffer一样，也是环形缓冲，但当缓冲满的时候，undo buffer中的内容会也会被刷新到磁盘；与redo log不同的是，磁盘上不存在单独的undo log文件，所有的undo log均存放在主ibd数据文件中（表空间），即使客户端设置了每表一个数据文件也是如此。
  2. InnoDB存储引擎在数据库每行数据的后面添加了三个字段
  • 6字节的事务ID(DB_TRX_ID)字段: 用来标识最近一次对本行记录做修改(insert|update)的事务的标识符, 即最后一次修改(insert|update)本行记录的事务id。
    至于delete操作，在innodb看来也不过是一次update操作，更新行中的一个特殊位将行表示为deleted, 并非真正删除。
  • 7字节的回滚指针(DB_ROLL_PTR)字段: 指写入回滚段(rollback segment)的 undo log record (撤销日志记录记录)。
    如果一行记录被更新, 则 undo log record 包含 ‘重建该行记录被更新之前内容’ 所必须的信息。
  • 6字节的DB_ROW_ID字段: 包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。
    结合聚簇索引的相关知识点, 我的理解是, 如果我们的表中没有主键或合适的唯一索引, 也就是无法生成聚簇索引的时候, InnoDB会帮我们自动生成聚集索引, 但聚簇索引会使用DB_ROW_ID的值来作为主键; 如果我们有自己的主键或者合适的唯一索引, 那么聚簇索引中也就不会包含 DB_ROW_ID 了 。

当前读和快照读

  1. MySQL的InnoDB存储引擎默认事务隔离级别是RR(可重复读), 是通过 “行排他锁+MVCC” 一起实现的, 不仅可以保证可重复读, 还可以部分防止幻读, 而非完全防止;
  2. 为什么是部分防止幻读, 而不是完全防止?
  • 效果: 在如果事务B在事务A执行中, insert了一条数据并提交, 事务A再次查询, 虽然读取的是undo中的旧版本数据(防止了部分幻读), 但是事务A中执行update或者delete都是可以成功的!!
  • 因为在innodb中的操作可以分为当前读(current read)和快照读(snapshot read):

  1. 快照读
    简单的select操作(当然不包括 select … lock in share mode, select … for update)

  2. 当前读

    • select … lock in share mode
    • select … for update
    • insert
    • update
    • delete

    在RR级别下，快照读是通过MVVC(多版本控制)和undo log来实现的，当前读是通过加record lock(记录锁)和gap lock(间隙锁)来实现的。
    innodb在快照读的情况下并没有真正的避免幻读, 但是在当前读的情况下避免了不可重复读和幻读!!!

参考

InnoDB怎么解决幻读的？

https://segmentfault.com/a/1190000012650596

https://www.jianshu.com/p/7e967d291c24

 

 

MySQL不可重复读和幻读的区别

不可重复读：A事务在执行过程中，B事务对数据进行了修改或删除，导致A两次读取的数据不一致；
重点在于update和delete（锁行即可解决），
幻读：A事务在执行过程中，B事务新增了符合A事务要查询的数据，导致A两次读取的数据不一致；
重点在于insert（需要锁表解决）。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

 
 

mysql 关于 不可重复读与幻读的解决方案

 不可重复读和幻读的区别

当然,   从总的结果来看,   似乎两者都表现为两次读取的结果不一致.

但如果你从控制的角度来看,   两者的区别就比较大
对于前者,   只需要锁住满足条件的记录
对于后者,   要锁住满足条件及其相近的记录

-----------------------------------------------------------

我这么理解是否可以？
避免不可重复读需要锁行就行
避免幻影读则需要锁表

------------------------

####不可重复读和幻读的区别####
很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复 读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会 发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

上文说的，是使用悲观锁机制来处理这两种问题，但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题。

####悲观锁和乐观锁####

  • 悲观锁

正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处 于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机 制，也无法保证外部系统不会修改数据）。

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

  • 乐观锁

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如 果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

要说明的是，MVCC的实现没有固定的规范，每个数据库都会有不同的实现方式，这里讨论的是InnoDB的MVCC。

posted @ 2015-05-17 12:51  南哥的天下  阅读(1560)  评论(0)  编辑  收藏  举报