数据库的并发控制机制,顾名思义,是用来控制数据库的并发操作的机制。控制的目的是为了保证数据完整和数据一致性。
何为数据一致性?在数据库的并发操作中,多个事务同时读取同一份数据,要保证多个事务读取的同一份数据是准确的。
可能数据的不一致更好理解。例如事务T1、T2同时更新余额为1000余额表, T1减100元,更新余额为900元,T2减500元,更新余额为500元;在T2不知道T1已经更新的情况下直接把余额更新为500元,丢失了T1对余额的更新,此时的数据不能反映真实情况,数据变得不一致。并发事务处理过程中,还可能出现事务T1修改了一条记录,事务T2读取该记录后,事务T1被撤销,此时T2读取的数据就与数据库中的数据不一致,该数据就为“脏”数据。
因此并发操作中,需要确保事务的隔离性以保证数据的一致性。那么如何确保事务的隔离性呢?接下来,我们将对DB2、MySQL和Oracle三种数据库的并发控制机制进行比较分析。
数据库的数据一致性支持机制:事务、锁、日志。
首先我们看看什么是事务。
事务:又称为交易,访问数据库系统的可恢复的最小单元。
事务在可执行的 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 将数据恢复到修改之前的样子。
1)潜在问题
事务为什么需要多种可以设置的隔离级别呢?通常,锁可以实现并发操作中事务的隔离,保证数据的一致性。锁提高了并发性能,但会带来潜在的问题:
在当前数据库的锁机制下不会导致理论意义上的丢失更新问题,但是实际上在所有多用户计算机系统环境下都有可能产生这个问题。例如:
这些问题往往和系统数据库的使用方式和形态有关。而设置事务的隔离级别,就是根据不同的场景来解决以上问题。比如上面所说的丢失更新问题,隔离级别中 SELECT…FOR UPDATE 即带有更新意图读的时候,步骤 1、2 都是要上写锁的,避免丢失更新的问题。下面详解数据库的隔离级别及其加锁方式。
2)数据库的隔离级别及其加锁方式
① SQL 标准定义的四个隔离级别
② 四种隔离级别会导致的问题
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
READ UNCOMMITTED | √ | √ | √ |
READ COMMITTED | × | √ | √ |
REPEATABLE READ | × | × | √ |
SERIALIZABLE | × | × | × |
③ 数据库中的隔离级别
DB2 中的隔离级别:
CS 提供了最大的并发性。但同一事务同一游标被处理两次,可能返回不同的结果,即不可重复度;CS 程序读取的行上有任何可更新游标时,其他任何应用程序都不能更新或删除该行。
CS 是 DB2 默认的隔离级别。在需要最大并行性但只能看到其他程序已 COMMIT 的数据时使用。
RR 是最高隔离级别,可以最好的保证数据一致性,但是大量锁定数据,会导致并发度大大降低,同时有可能超过系统定义的持有锁数量的限制。
相当于标准定义隔离级别中的 SERIALIZABLE 相比,上锁范围一致。
相比 RR,RS 锁定数据的数量大大减少,并发度得到提升。比较适合在并发环境下运行,但只适合在同一事物中不会多次发出相同查询,或不要求相同查询获得相同结果的程序,避免发生幻读。
DB2 的 RS 和标准定义隔离级别中的 REPEATABLE READ(重复读)类似,避免了脏读,但是会出现幻读问题。
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(提交读)。
事务隔离级别是并发控制的整体解决方案,其实际上是综合利用各种类型的锁和行版本控制来解决并发问题。
这里我们主要看数据库中的基本锁。
DB2、MySQL、Oracle 都支持 S-LOCK 和 X-LOCK,DB2 还支持 U-LOCK。
如上,数据库在各种隔离级别下,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 隔离级别下使用一致性非锁定读也有同样的功能。
隔离级别 READ COMMITED 和 REPEATABLE READ(MySQL 的默认隔离级别)都使用一致性非锁定读, SELECT 时不上锁,那么如何保证事务的隔离性呢?这两种隔离级别采用快照数据的方式保证隔离性。读取时对于上了 X 锁的数据,都会去读取行的一个快照数据。快照数据是指该行的之前版本的数据,通过 undo 段实现。而 undo 段用来在事务中回滚数据,因此快照数据本身没有额外的开销。
READ COMMITED 和 REPEATABLE READ 两种隔离级别在读快照数据时的区别是,RC 总是读取最新的快照数据,所以可能会发生不可重复读,即第二次读取的数据和第一次不一致;而 RR 总是读取事务开始时的快照,所以不会发生不可重复度。
非锁定读机制不会等待行上 X 锁的释放,极大的提高了数据库的并发性。是 InnoDB 的默认读取方式。
并发控制在保证数据一致性的前提下提供最大的并发性,而保证数据一致性的前提就是保证事务的隔离性,事务的隔离性和并发性是成反比的,隔离级别越高,并发性越低。所以程序要视并发性和隔离性的轻重选择隔离级别。
作者介绍:
李为,现任金融行业核心业务系统 DBA,主要涉及 DB2、Oracle、MySQL 等数据库开发工作。
原文链接:
一文掌握 Oracle、MySQL、DB2 并发控制机制的异同
本文由 dbaplus 社群授权转载。
领取专属 10元无门槛券
私享最新 技术干货