作者介绍
李为,现任金融行业核心业务系统DBA,主要涉及DB2、Oracle、MySQL等数据库开发工作。
数据库的并发控制机制,顾名思义,是用来控制数据库的并发操作的机制。控制的目的是为了保证数据完整和数据一致性。
何为数据一致性?在数据库的并发操作中,多个事务同时读取同一份数据,要保证多个事务读取的同一份数据是准确的。
可能数据的不一致更好理解。例如事务T1、T2同时更新余额为1000余额表, T1减100元,更新余额为900元,T2减500元,更新余额为500元;在T2不知道T1已经更新的情况下直接把余额更新为500元,丢失了T1对余额的更新,此时的数据不能反映真实情况,数据变得不一致。并发事务处理过程中,还可能出现事务T1修改了一条记录,事务T2读取该记录后,事务T1被撤销,此时T2读取的数据就与数据库中的数据不一致,该数据就为“脏”数据。
因此并发操作中,需要确保事务的隔离性以保证数据的一致性。那么如何确保事务的隔离性呢?接下来,我们将对DB2、MySQL和Oracle三种数据库的并发控制机制进行比较分析。
数据库的数据一致性支持机制:事务、锁、日志。
首先我们看看什么是事务。
一、事务
事务:又称为交易,访问数据库系统的可恢复的最小单元。
1、事务的ACID
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)潜在问题
事务为什么需要多种可以设置的隔离级别呢?通常,锁可以实现并发操作中事务的隔离,保证数据的一致性。锁提高了并发性能,但会带来潜在的问题:
在当前数据库的锁机制下不会导致理论意义上的丢失更新问题,但是实际上在所有多用户计算机系统环境下都有可能产生这个问题。例如:
这些问题往往和系统数据库的使用方式和形态有关。而设置事务的隔离级别,就是根据不同的场景来解决以上问题。比如上面所说的丢失更新问题,隔离级别中SELECT…FOR UPDATE即带有更新意图读的时候,步骤1、2都是要上写锁的,避免丢失更新的问题。下面详解数据库的隔离级别及其加锁方式。
2)数据库的隔离级别及其加锁方式
① SQL标准定义的四个隔离级别
② 四种隔离级别会导致的问题
③ 数据库中的隔离级别
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(提交读)。
二、锁
事务隔离级别是并发控制的整体解决方案,其实际上是综合利用各种类型的锁和行版本控制来解决并发问题。
这里我们主要看数据库中的基本锁。
1、锁的类型
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的默认读取方式。
三、小结
并发控制在保证数据一致性的前提下提供最大的并发性,而保证数据一致性的前提就是保证事务的隔离性,事务的隔离性和并发性是成反比的,隔离级别越高,并发性越低。所以程序要视并发性和隔离性的轻重选择隔离级别。
本文转载自微信公众号「DBAplus社群」,可以通过以下二维码关注。转载本文请联系DBAplus社群公众号。
目录 1. C语言文件接口(库函数) 1.1 fopen 1.2 fclose 1.3 fread 1.4 fwrite 1.5...
首先到这里下载其源码。里面东西挺多的,我们基本上可以把它放到两个文件夹就是...
本文实例为大家分享了vue实现按钮切换图片的具体代码,供大家参考,具体内容如下...
在大三的时候,一直就想搭建属于自己的一个博客,但由于各种原因,最终都不了了...
今天看到个不错的网页播放器,感觉不错,大家可以测试 我写的一个播放器网页: ...
本文实例为大家分享了javascript实现倒计时提示框的具体代码,供大家参考,具体...
由于固态驱动器(SSD)的速度比传统的硬盘驱动器(HDD)快得多,并且价格越来越便宜...
这5个PHP编程中的不良习惯,一定要改掉 PHP世界上最好的语言! 测试循环前数组是...
MFC项目在vs2017编译正常无报错,但是升级vs2019后一打开项目就报如下错误。 项...
目录 读者基础 ?微服务架构梳理 https://www.coder4.com/homs_online/ ? ? 读者...