SQL与Oracle对数据库事务处理的差异性

1、SQL和Oracle 对数据库事务处理的差异性作者：Jake, 岀处:IT专家网论坛，责任编辑：陈子琪,2009-10-11 13:00在吉日嘎拉的软件编程走火入魔之：数据库事务处理入门（适合初学者阅读）文章中关于MS SQL Server 和Oracle对数据库事务处理的差异性引起一些争论，因此记录我对数据 库事务处理的想法。背景在吉日嘎拉的软件编程走火入魔之：数据库事务处理入门（适合初学者阅读）文章中关于MS SQL和Oracle对数据库事务处理的差异性引起一些争论，因此记录我对数据库事务处理的想法。简介本文讲述 MS SQL Server 和Oracle对数据库事务处理的差异性，以及Or

2、acle如何对事务处理的实现。什么是事务数据库事务（Transaction）是一组数据库操作的处理单元。事务符合ACID的特性：:原子性，要么全部要么一无所有。All or None.Con siste nt: 致性，所有依赖关系以及约束一致。Isolated:分离性，不同事务不互相影响。Durable:持久性，提交事务的数据需要持久化。为什么要使用事务实现事务主要有两大功能：1保证数据库的consistent（ 致性状态），保持所有依赖关系以及约束一致）。哪怕数据库管理系统出现故障时（例如断电），也能恢复到一致性状态。例如一个银行转帐系统，张 三给李四转圆RMB，张三帐号上减3000和李四帐

3、号上加3000需要同时完成，否则系统 的帐就不平了。也例如有些销售系统的汇总表和明细表，是一个主表和一个从表，需要同步 更新。2. 并发时分离不同事务操作。例如编辑过程中的数据不给其他事务查询到。这也是相对 的，在特效需求下可能要支持dirty read（ 脏读），但不是这里讨论的范围了。SQL Server 2008的事务类型1. 自动提交事务 Autocommit Transactions这是SQL Server 默认的事务类型，每一条单独的SQL语句（SQL statement）都是单独的一个事务，语句执行完毕后自动提交。调用方不需要手工控制事务流程。2. 显示事务 Explicit T

4、ransactions调用方需要调用 API或者使用T-SQL的BEGIN 语句来打开事务。需要调用或者 TRANSACTION来提交或者回滚。3. 隐式事务 Implicit Transactions使用IMPLICIT_TRANSACTIONS ON把事务模式变成隐式模式。调用方不需要执行BEGIN TRANSACTION语句来打开事务。数据库引擎执行到SQL语句的时候自动打开事务。调用方需要调用 COMMIT 或者ROLLBACK TRANSACTION来提交或者回滚。当数 据库引擎执行下一个 SQL语句时又自动打开一个新事务。参考：Controlling Transactions (D

5、atabase Engine)Oracle 的事务类型Oracle的事务处理类型有点像 SQL的隐式事务。当执行到第一个可执行的SQL语句时自动打开事务，然后需要调用方执行或者来提交或者回滚事务，如果有DDL语句，Oracle也会自动提交事务的。参考:ManagementOracle 的事务的实现Oracle的结构分逻辑上和物理上的区别。逻辑上的结构是表空间，而物理上的结构是数据文件。逻辑实现Oracle 下实现事务在逻辑上是由Un do来实现的。Undo Tablespace 包含UndoSegeme nts(段),而Undo Segeme nts包含Undo 。 Un do Data 是支

6、持事务的逻辑单元。Undo Datahttp:.c n/TLimages/picview/?/imagelist/2009/282/08xwoop4pi2e.p ngUndo dta ImA copy of original premodification, data Captured for every transaction that changes data Retained at lMt until th trwMetlon h ended Used to support:Rollback operations ReddconsiMent and

7、flashback queries-Recovery from failed trAnsoctlons图源自于Oracle 10g: Administration Workshop Iflashback 查询,Undo Data 用于保存修改前后的数据，以支持回滚，长时间查询，和 以及失败事务恢复的功能。由于有了 Un do Data ，回滚事务变得容易了，因为Un do Data 保存了修改前后的数据， 保证了事务的原子性。失败事务恢复和事务回滚类似，当网络或者其他原因导致事务异常停 止，数据库引擎可以恢复到一致性状态。同时Un do Data也支持长时间查询(Read-co nsiste

8、nt)，例如有表格 T,主键为，有字段为f1，数据如下，尽管只有4条数据，假设需要很长时间进行查询。keyfl1A23匚4D事务一开始查询，一直没结束，而事务二开始修改为1的数据为Z,事务二进行提交，数据变成下面的表格。但是事务一查询结束的时候还是读出A,B,C,D，因为查询是从 Undo中读出快照。keyfl1Z283C4DFlashback 查询是Oracle 10g引进的功能，可以查询出提交之后修改之前的数据，例如上面例子事务三在事务二提交后想查询出A,B,C,D可以通过Flashback 查询来完成。这也是有Un do来支持的。物理实现从上面的逻辑实现看，只是知道了事务以及Un do

9、Data的作用，还不清楚 Oracle对事务的支持到底怎么实现的。下面从物理结构上讲述Oracle怎么对事务进行支持。请先看一个物理结构图。Oracle Instance ManagementSGAShored Poo*Streams PoolLarge PoolJava PoolDatabaseBuffer Cache| |Writer| LGyVR |Archtv-erARCOI SysierrTII Process IDatabase| LvyMonitor MonitorWriterWriter| 删朋 | | PMON D舸 | L丽R |peintCKPT图源自于Oracle 10

10、g: Administration Workshop I为了简化，我只是用个人的语言讲述和事务有关的部件。SGA可以理解为全局内存。其中Buffer存放的是从数据文件中读取的数据缓存。紫色的圆柱体为数据文件。Redo LogBuffer为重做日志缓存，也就是保存日志的内存块，一切的数据的修改都会记录在Redo LogBuffer里面。例如用回事务二更新为1数据的例子。当事务二更新key为1数据从A到乙Oracle数据库引擎会把 key为1的数据的rowid，修改前数据A以及修改后数据 Z都记录 在Redo Log Buffer 里面。如果事务二继续更新 key为2的数据为Y,那么key为2的数

11、 据的rowid，修改前数据 B以及修改后数据 Y也记录到Redo Log Buffer里面。每一笔数据都记录，而且是流水线性记录。一旦事务二进行提交，LGWR （Log Writer ，日志写进程）就会把Redo Log Buffer的数据按顺序写到 Log Datafile 里面，也就是日志数据文件里面，当写日志文件完成，Oracle数据库引擎会生成一个SCN（change number，系统更新号）,到这时候Oracle数据库引擎会通知调用方提交完成了。这里可以看到Oracle在提交的时候不必要把更新数据写回数据文件，而是写到日志文件里面。因为顺序写线性的日志文件速度 快很多，而写数据文

12、件是需要随机读写。由于线性记录和SCN号控制，数据库引擎可以通过redo log（重做日志文件）的操作得到最新的数据。 当然在Checkpoint的时候数据文件是最终还是会更新的，只是说事务提交的时候更新数据文件不是必须步骤，这样能很大的提高 性能。由于这个机制，回滚变得很简单，要读没提交前之前的SCN是很容易的事情。对于初学者关于数据库事务处理的建议我自己也是从新手一步步走过来，现在也不是老鸟，算是有点经验，如果是刚入门的同 学，你觉得有用就看一下，没用就过了。对于数据库事务的处理，开始的时候不需要很深入了解数据库的原理，当然以后还是需要了解的，优先级排后而已。高优先级如下：首先，事务不是什

13、么高深神秘的东西，我从入行开始所做的所有系统，包括现在的嵌入 式系统，都用到事务。我并不觉得大部分系统事务有什么问题，只是一些约束和同步机制， 真的有问题从自身系统设计角度看，不一定说从数据库技术角度去找解决方法。例如Oracle的长时间查询如果 Undo （历史数据）给覆盖了，Oracle会抛出异常ORA-O1555: snapshot too old ，如果出现这种问题，我会从自身系统设计角度入手，为什么有那么大 的查询，为什么在这个查询中其他事务会更新数据，这些查询是否只是查一次就够了，查的 过程是否需要锁住表等等。然而这个问题可以通过数据库调优解决，但是我想问题的角度首 先是从自身系统设计出发。第二，要知道的是不同数据库的事务类型的区别，例如MS SQL Sever 是默认是自动提交事务，用的时候需