一文掌握 Oracle、MySQL、DB2 并发控制机制的异同

本文由 dbaplus 社群授权转载。

数据库的并发控制机制，顾名思义，是用来控制数据库的并发操作的机制。控制的目的是为了保证数据完整和数据一致性。

何为数据一致性？在数据库的并发操作中，多个事务同时读取同一份数据，要保证多个事务读取的同一份数据是准确的。

可能数据的不一致更好理解。例如事务 T1、T2 同时更新余额为 1000 余额表， T1 减 100 元，更新余额为 900 元，T2 减 500 元，更新余额为 500 元；在 T2 不知道 T1 已经更新的情况下直接把余额更新为 500 元，丢失了 T1 对余额的更新，此时的数据不能反映真实情况，数据变得不一致。并发事务处理过程中，还可能出现事务 T1 修改了一条记录，事务 T2 读取该记录后，事务 T1 被撤销，此时 T2 读取的数据就与数据库中的数据不一致，该数据就为“脏”数据。

因此并发操作中，需要确保事务的隔离性以保证数据的一致性。那么如何确保事务的隔离性呢？接下来，我们将对 DB2、MySQL 和 Oracle 三种数据库的并发控制机制进行比较分析。

数据库的数据一致性支持机制：事务、锁、日志。

首先我们看看什么是事务。

一、事务

事务：又称为交易，访问数据库系统的可恢复的最小单元。

1、事务的 ACID

• 原子性（Atomicity）： 事务为一个整体的工作单元，事务对数据库的操作要么全部执行，要么全部取消；

• 一致性（Consistency）： 事务完成时，所有数据都保持一致状态；

• 隔离性（Isolation）： 事务所做的修改必须与其他事务所做的修改隔离。事务查看数据时数据的状态要么为其他事务修改之前要么为其他事务修改之后，不会为中间状态。即多个事务不能同时修改同一份数据；

• 持久性（Durability）： 事务提交后，对数据库所做的修改会永久保存。

2、事务的初始化和终止

事务在可执行的 SQL 第一次执行时会自动初始化，事务一旦初始化，就必须终止（COMMIT 或 ROLLBACK）。

1）关于事务的 COMMIT 和 ROLLBACK

多数情况下，事务通过执行 COMMIT 或 ROLLBACK 终止事务。执行 COMMIT 语句后，事务初始化后对数据库做出的所有改变都会变成永久的；执行 ROLLBACK 语句后，事务初始化后对数据库做出的所有改变都会被撤销，数据库返回事务开始之前的状态。

2）关于不成功的事务的结果

上面说了当事务被 COMMIT 或 ROLLBACK 终止语句后会发生什么，如果事务完成之前系统发生故障，会发生什么？这种情况下，数据库管理器将撤销所有未 COMMIT 的修改，从而恢复数据的一致性。

DB2 中通过 ACTIVE LOG 日志文件实现撤销修改。日志文件包含关于事务执行的每个语句的信息，以及事务是否被成功 COMMIT 或 ROLLBACK 的信息。

MySQL 和 Oracle 利用 undo log 撤销修改。undo log 记录了行的修改操作，执行事务中由于某种原因失败，或使用 ROLLBACK 时，就可以利用 undo log 将数据恢复到修改之前的样子。

3、事务的隔离级别

1）潜在问题

事务为什么需要多种可以设置的隔离级别呢？通常，锁可以实现并发操作中事务的隔离，保证数据的一致性。锁提高了并发性能，但会带来潜在的问题：

• 脏读： 当前事务可以读到另外一个事务中未提交的数据。

• 不可重复读： 在一个事务内读到的同一条数据是不一样的。

• 幻读： 事务 A 在相同条件下第二次读取时读到新插入的数据。

• 丢失更新： 一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。 例如：

• 事务 T1 将行记录修改为 V1，事务 T1 未提交。

• 事务 T2 将行记录修改为 V2，事务 T2 未提交。

• 事务 T1 提交。

• 事务 T2 提交。

在当前数据库的锁机制下不会导致理论意义上的丢失更新问题，但是实际上在所有多用户计算机系统环境下都有可能产生这个问题。例如：

• 事务 T1 查询一行数据，放入本地内存，显示给 User1。

• 事务 T2 查询一行数据，放入本地内存，显示给 User2。

• User1 修改这行记录，更新数据库并提交。

• User2 修改这行记录，更新数据库并提交。

这些问题往往和系统数据库的使用方式和形态有关。而设置事务的隔离级别，就是根据不同的场景来解决以上问题。比如上面所说的丢失更新问题，隔离级别中 SELECT…FOR UPDATE 即带有更新意图读的时候，步骤 1、2 都是要上写锁的，避免丢失更新的问题。下面详解数据库的隔离级别及其加锁方式。

2）数据库的隔离级别及其加锁方式

① SQL 标准定义的四个隔离级别

• READ UNCOMMITTED： 未提交读。事务可以看到其他事务所有未提交的数据。读取数据不加锁；

• READ COMMITTED： 提交读。事务只可以看到其他事务已经提交的数据；

• REPEATABLE READ： 重复度。锁定事务引用的符合检索条件的部分行，其他事务不可修改这些行，但可执行 INSERT 操作。即可能出现幻读；

• SERIALIZABLE： 可串行化。强制的进行排序，在每个读数据行上添加锁，所有事务依次逐个执行，事务之间不会产生干扰。事务提交后释放锁。会导致大量超时现象和锁竞争。

② 四种隔离级别会导致的问题

③ 数据库中的隔离级别

DB2 中的隔离级别：

• CS（Cursor Stability）：游标稳定性。 逐行锁定数据，该行数据未修改时，锁定解除，继续加锁读取下一行，该行数据有修改时，则该行锁定持续到事务终止。CS 的程序不能查看其他程序未 COMMIT 的更改。

CS 提供了最大的并发性。但同一事务同一游标被处理两次，可能返回不同的结果，即不可重复度；CS 程序读取的行上有任何可更新游标时，其他任何应用程序都不能更新或删除该行。

CS 是 DB2 默认的隔离级别。在需要最大并行性但只能看到其他程序已 COMMIT 的数据时使用。

• RR（Repeatable Read）：可重复读。 RR 会锁定事务引用的所有行，直到 COMMIT。其他程序不能修改该数据，如果一条数据被访问两次，返回相同的结果。

RR 是最高隔离级别，可以最好的保证数据一致性，但是大量锁定数据，会导致并发度大大降低，同时有可能超过系统定义的持有锁数量的限制。

相当于标准定义隔离级别中的 SERIALIZABLE 相比，上锁范围一致。

• RS（Read Stability）：读稳定性。 RS 会锁定事务引用的所有行中符合检索条件的部分行。其他程序不可修改，但可执行 INSERT 操作，所以同一事务中，如果数据被访问两次可能返回新插入的数据，即幻读，但是旧数据不会有改变。

相比 RR，RS 锁定数据的数量大大减少，并发度得到提升。比较适合在并发环境下运行，但只适合在同一事物中不会多次发出相同查询，或不要求相同查询获得相同结果的程序，避免发生幻读。

DB2 的 RS 和标准定义隔离级别中的 REPEATABLE READ（重复读）类似，避免了脏读，但是会出现幻读问题。

• UR（Uncommitted Read）：未提交读，也就是“脏”读。 UR 不会加任何锁，可以读数据库中的任何数据，包含已修改但未 COMMIT 的数据。读的数据可能与真实的数据有一定差距。

UR 级别最常用于只读表上的查询，或者只执行查询且不关心能否读到其他程序未 COMMIT 的数据时常用。

UR 相当于标准定义隔离级别中的 READ UNCOMMITTED（未提交读）。

MySQL 支持标准定义的四种隔离级别 ，默认的隔离级别为 REPEATABLE READ（重复度），但是与标准 SQL 不同的是，MySQL 的 InnoDB 存储引擎在 REPEATABLE READ 的隔离级别下，使用 Next-Key Lock（锁定一个范围，并锁定记录本身），因此避免幻读的产生。所以说 InnoDB 存储引擎在 REPEATABLE READ 的隔离级别下已经能保证事务的隔离性要求，即达到 SQL 标准的 SERIALIZABLE 隔离级别。

Oracle 数据库支持 READ COMMITTED（提交读）和 SERIALIZABLE 这两种事务隔离级别。 默认的隔离级别是 READ COMMITTED（提交读）。

二、锁

事务隔离级别是并发控制的整体解决方案，其实际上是综合利用各种类型的锁和行版本控制来解决并发问题。

这里我们主要看数据库中的基本锁。

1、锁的类型

• S-LOCK： 共享锁。又叫读锁，当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加多个；

• X-LOCK： 排他锁。又叫写锁。SQL INSERT/UPDATE/DELETE 语句执行时会上 X-LOCK。排他锁只可以加一个，和其他的排他锁共享锁都相斥；

• U-LOCK： 修改锁。CURSOR SELECT 有 UPDATE OF 子句时，FETCH 时对读出的记录，会上 U-LOCK。

DB2、MySQL、Oracle 都支持 S-LOCK 和 X-LOCK，DB2 还支持 U-LOCK。

2、事务隔离级别中读数据时的锁类型

如上，数据库在各种隔离级别下，SQL 执行 INSERT/UPDATE/DELETE 语句时都会上 X-LOCK，那么在读数据时如何上锁呢?

DB2 和 MySQL 在 Uncommitted Read 隔离级别下，不加任何锁。

1）DB2

DB2 在另外三种 CS、RR、RS 隔离级别时，SELECT 语句，或 CURSOR SELECT 无 UPDATE OF 子句，FETCH 时对读出的记录会上 S-LOCK，不同的是，CS 在读取下一行数据时就释放上一行的锁，RR、RS 在事务提交时才释放锁；SELET…FOR UPDATE 对读取的数据都是加 U 锁，CS 在读取下一行数据时就释放上一行的锁，RR、RS 在事务提交时才释放锁；INSERT/UPDATE/DELETE 语句执行时会上 X-LOCK，CS、RR、RS 都是在事务提交时才释放 X 锁，其他事务不能对已锁定的行加任何锁。

2）MySQL

MySQL 的 InnoDB 在隔离级别 READ COMMITED 和 REPEATABLE READ（MySQL 的默认隔离级别）下 SELECT 时不上锁，即 MySQL 中的一致性非锁定读；只有指定 SELECT…LOCK IN SHARE MOAD 才对记录上 S-LOCK，SERIALIZABLE 隔离级别下 SELECT 对记录上 S-LOCK；三种隔离级别下，SELET…FOR UPDATE 对读取的数据都是加 X 锁，在 MySQL 中叫做一致性锁定读。

3）Oracle

Oracle 中只支持 READ COMMITED 和 SERIALIZABLE 隔离级别。这两种隔离级别下的锁机制和 InnoDB 一致。Oracle 中不需要 READ UNCOMMITTED 隔离级别，是因为 READ UNCOMMITTED 主要功能是提高只读时的并发性，而 Oracle 在 READ COMMITED 隔离级别下使用一致性非锁定读也有同样的功能。

3、一致性非锁定读

隔离级别 READ COMMITED 和 REPEATABLE READ（MySQL 的默认隔离级别）都使用一致性非锁定读， SELECT 时不上锁，那么如何保证事务的隔离性呢？这两种隔离级别采用快照数据的方式保证隔离性。读取时对于上了 X 锁的数据，都会去读取行的一个快照数据。快照数据是指该行的之前版本的数据，通过 undo 段实现。而 undo 段用来在事务中回滚数据，因此快照数据本身没有额外的开销。

READ COMMITED 和 REPEATABLE READ 两种隔离级别在读快照数据时的区别是，RC 总是读取最新的快照数据，所以可能会发生不可重复读，即第二次读取的数据和第一次不一致；而 RR 总是读取事务开始时的快照，所以不会发生不可重复度。

非锁定读机制不会等待行上 X 锁的释放，极大的提高了数据库的并发性。是 InnoDB 的默认读取方式。

三、小结

并发控制在保证数据一致性的前提下提供最大的并发性，而保证数据一致性的前提就是保证事务的隔离性，事务的隔离性和并发性是成反比的，隔离级别越高，并发性越低。所以程序要视并发性和隔离性的轻重选择隔离级别。

作者介绍：

李为，现任金融行业核心业务系统 DBA，主要涉及 DB2、Oracle、MySQL 等数据库开发工作。

原文链接：

一文掌握Oracle、MySQL、DB2并发控制机制的异同