事务隔离机制（一篇文章带你学习mysql和oracle事务隔离机制）

事务的概念

一个事务中的一系列的处理操作操作要么全部成功，要么一个都不做。在数据库操作中，一项事务（Transaction）是由一条或多条操作数据库的 SQL 语句组成的一个不可分割的工作单元。

那么，事务的处理结束就会有两种：1. 当事务中的所有步骤全部成功执行时，事务提交，成功；2. 如果其中任何一个步骤失败，该事务都将发生回滚操作，撤消已执行的所有操作。

事务四大特性

数据库事务四特性：原子性、一致性、隔离性和持久性，也就是人们熟知的 ACID 特性。在实际生产应用中，数据库中的数据是要被多用户共享/访问，而在多个用户同时操作相同数据时，可能会出现一些事务的并发问题，这就有了事务隔离性。今天，我们重点将围绕事务隔离性学习。

事务隔离级别

SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

读未提交（READ UNCOMMITTED）

读提交 （READ COMMITTED）

可重复读 （REPEATABLE READ）默认

串行化 （SERIALIZABLE）

Oracle 只支持

读提交 （READ COMMITTED）默认

串行化 （SERIALIZABLE）

只读（read only）

学习一个机制的最好办法就是假设这个机制不在会发生什么问题

没有隔离级别出现的三个问题

脏读

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。

不可重复读

不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。

幻读

当同一个查询在不同的时间产生不同的结果集时，事务中就会出现所谓的幻象问题。例如，如果 SELECT 执行了两次，但第二次返回了第一次没有返回的行，则该行是“幻像”行。

MySQL的隔离机制

Mysql修改隔离级别

修改隔离级别的语句是：set [作用域] transaction isolation level [事务隔离级别]，
SET  [SESSION | GLOBAL]  TRANSACTION ISOLATION LEVEL 

  {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ |        SERIALIZABLE}。

读未提交

在此级别下，一个事务可以读取到另一个事务尚未提交的数据。

这可能导致脏读（Dirty Read），即读取到其他事务可能会回滚的数据。

此级别隔离性最低，但并发性能最高。

脏读（读未提交）

读已提交

在这个级别，一个事务只能读取另一个已提交事务的数据。

这解决了脏读问题，但可能出现不可重复读，即在同一事务内多次读取同一数据可能得到不同的结果，因为其他事务在此期间可能对数据进行了修改并提交。

大多数数据库系统的默认隔离级别是Read Committed。

不可重复读

（读已提交）

可重复读

在这个级别，同一事务内的多次读取将看到相同的数据行，无论其他事务如何修改这些数据。

这解决了脏读和不可重复读问题，但可能出现幻读，即一个事务在读取某个范围内的记录时，另一个事务插入了一些新的记录，导致前一个事务在再次读取该范围时看到了一些“幻影”记录。

MySQL的InnoDB存储引擎默认使用Repeatable Read作为隔离级别。

幻读

（可重复读）

串行化读

这是最严格的隔离级别，它强制事务串行执行，从而避免了脏读、不可重复读和幻读问题。

在这个级别下，事务之间是完全隔离的，每个事务都必须等待前一个事务完成后才能执行，这大大降低了并发性能。

因此，尽管Serializable提供了最高级别的数据一致性保证，但在需要高并发性能的应用场景中通常不使用它。

Mysql是如何实现四种隔离级别

首先说读未提交，它是性能最好，也可以说它是最野蛮的方式，根本谈不上什么隔离效果，可以理解为没有隔离。

再来说串行化。读的时候加共享锁，也就是其他事务可以并发读，但是不能写。写的时候加排它锁，其他事务不能并发写也不能并发读。

最后说读提交和可重复读。这两种隔离级别是比较复杂的，既要允许一定的并发，又想要兼顾的解决问题。（mvcc + read view）

MVCC

全称Multi-Version Concurrency Control，即多版本并发控制，MVCC 的一个主要目的是提供事务级的读一致性。

在 MVCC 中，每当数据被修改时，数据库不会直接覆盖原始数据，而是保留原始数据的一个版本，并创建一个新的数据版本。这样，不同的事务就可以读取不同版本的数据，而不会相互干扰。当事务完成时，它所做的更改会提交，新的数据版本会成为正式版本。

隐式字段

关于 DB_ROLL_PTR 与 Undo日志 的配合工作，具体流程如下：

1. 在更新或删除操作之前，MySQL会将旧值写入Undo日志中。
2. 当事务需要回滚时，MySQL会根据事务的Undo日志记录，通过 DB_ROLL_PTR 找到对应的Undo日志。
3. 根据Undo日志中记录的旧值，MySQL将旧值恢复到相应的数据行中，实现数据的回滚操作。

Read View

一致性视图，全称 Read View ，是用来判断版本链中的哪个版本对当前事务是可见的

Read View 就是事务进行快照读操作时候生成的读视图，在该事务执行快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID

像不加锁的 select 操作就是快照读

当前读

当前读包含的 SQL 语句如下：

update , delete , insert

select......for update

select......lock in share mode

当前读, 对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问题。那你有想过，为什么 Mysql 要对当前读的这些 SQL 加锁？这里有个例子：假设要 update 一条记录，但另一个事务已经delete这条数据且commit了，如果不加锁就会产生冲突。所以update的时候肯定要是当前读，得到最新的信息并且锁定相应的记录。

读已提交的实现

1、如果被访问版本的 DB_TRX_ID 属性值与 Read View 中的 creator_trx_id值相同，表示当前事务正在访问自己所修改的记录，因此该版本可以被当前事务访问。

2、如果被访问版本的 DB_TRX_ID 属性值小于 Read View 中的min_trx_id 值，说明生成该版本的事务在当前事务生成 Read View 之前已经提交，因此该版本可以被当前事务访问。

3、如果被访问版本的 DB_TRX_ID 属性值大于或等于 Read View 中的 max_trx_id值，说明生成该版本的事务在当前事务生成 Read View 之后才提交，因此该版本不能被当前事务访问。

4、如果被访问版本的 DB_TRX_ID 属性值位于 Read View 的 min_trx_id和 max_trx_id之间（包括边界），则需要进一步检查 DB_TRX_ID 是否在m_ids 列表中。如果在列表中，说明在创建ReadView时生成该版本的事务仍处于活跃状态，因此该版本不能被访问；如果不在列表中，说明在创建 Read View 时生成该版本的事务已经提交，因此该版本可以被访问。

可重复读与读已提交的区别就是：在读已提交下，每个快照读都会生成并获取最新的read view。可重复读隔离级别下在第一个快照读时创建read view，之后快照读获取的都是同一个read view。

可重复读的锁

（有关MySQL的锁可以看我的上一篇文章）

InnoDB有三种行锁的算法,都是索引上的锁，需要开RR隔离级别：

1、Record Lock 记录锁：单个行记录(索引记录)上的锁。

2、Gap Lock 间隙锁：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

3、Next-Key Lock ：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

因为InnoDB对于当前读行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围（GAP）。
假设索引a有1，3，5，8，11，其记录的GAP的区间如下：是一个左开右闭的空间

（-∞,1]，(1,3]，(3,5]，(5,8]，(8,11]，(11,+∞）

执行一条sql ：select * from t where a = 3 for update；

这条sql锁住的范围有 (1,3）3，(3,5] 即 当前行+两边的间隙

但是注意，对于可重复读默认使用的就是next key lock，但是对于“唯一索引” ，比如主键的索引，next key lock会降级成行锁Record Lock ，即仅锁住索引本身，而不会锁住一个区间。

如果没有索引，上述UPDATE语句将会锁住表格中的每一条记录和每个间隙，其它UPDATE、INSERT和DELETE语句都无法执行，相当于锁表+锁所有的间隙 。

Oracle如何实现数据一致性

读已提交

一下是官方介绍Data Concurrency and Consistency (oracle.com)

As the database retrieves data blocks on behalf of a query, the database ensures that the data in each block reflects the contents of the block when the query began. The database rolls back changes to the block as needed to reconstruct the block to the point in time the query started processing.

The database uses an internal ordering mechanism called an SCN to guarantee the order of transactions. As the statement enters the execution phase, the database determines the SCN recorded at the time the query began executing. In Figure 9-1, this SCN is 10023. The query only sees committed data with respect to SCN 10023. SELECT

In Figure 9-1, blocks with SCNs after 10023 indicate changed data, as shown by the two blocks with SCN 10024. The statement requires a version of the block that is consistent with committed changes. The database copies current data blocks to a new buffer and applies undo data to reconstruct previous versions of the blocks. These reconstructed data blocks are called consistent read (CR) clones.

In Figure 9-1, the database creates two CR clones: one block consistent to SCN 10006 and the other block consistent to SCN 10021. The database returns the reconstructed data for the query. In this way, Oracle Database prevents dirty reads.

个人理解如下：

当数据库执行一个查询时，它会根据查询的需求从存储中检索相应的数据块。

在并发环境中，为了确保每个查询都能看到一致的数据视图，它会根据需要回滚或应用某些更改，以重建查询开始时的数据块状态。

如果某个事务在查询开始后修改了数据块，并且这个修改对于当前查询是不可见的（例如，因为该事务尚未提交），那么数据库会回滚这些更改，以确保查询看到的是正确的、一致的数据版本。

数据库使用了一种称为SCN（系统更改号，System Change Number）的内部排序机制来保证事务的顺序。当语句进入执行阶段时，数据库会确定查询开始执行时记录的SCN。在图中，这个SCN是10023。查询只会看到相对于SCN 10023已提交的数据。

这意味着，在执行查询时，数据库会参考SCN 10023来确定哪些数据对查询是“可见”的。只有在这个SCN之前（包括这个SCN）已经被提交的数据才会被查询返回，之后提交的数据变更则不会影响到这个查询的结果。这样做可以确保查询的一致性视图

当事务需要读取数据时，数据库不是直接读取最新的数据块，而是将这些数据块复制到buffer cache中。在复制的数据块上，数据库会应用撤销数据（undo data）来撤销在该事务开始之后发生的其他事务的更改。通过应用undo data，数据库能够重建出与事务开始时一致的数据块版本。这些重建后的数据块被称为consistent read (CR) clones 一致性读（CR）克隆 （CR块）

在图中，数据库创建了两个 CR 块：一个块与 SCN 10006 一致，另一个块与 SCN 10021 一致。数据库返回查询的重建数据。通过这种方式，Oracle 数据库可以防止脏读

串行化读

事务只看到事务开始时提交的更改以及事务本身所做的更改。该环境使它看起来好像没有其他用户在修改数据库中的数据

当可序列化事务尝试更新或删除在可序列化事务开始后提交的其他事务更改的数据时，数据库将生成错误：

ORA-08177: Cannot serialize access for this transaction

只读隔离级别

只读隔离级别类似于可序列化隔离级别，但只读事务不允许在事务中修改数据

只读事务可用于生成报告，其中的内容必须与事务开始的时间一致

Oracle 数据库通过根据需要从undo segment中重建数据来实现读取一致性，很有可能undo data已经被覆盖，也就会出现常见的ora-01555快照过旧。 。