1、第四章 数据库事务SQL Server 20002005 数据库系统通过事务保证多个数据库操作在一起处理，而事务使用锁定技术来防止其他数据库用户来更新或读取末完成事务中的数据。同时，SQL Server 数据库系统为了提高自身的性能，实现多用户之间数据的共享，采用并发控制策略来实现多个任务的并行运行。4.1 SQL Server 中事务的定义1实验目的熟悉 SQL Server 的事务控制语言，能够熟练使用事务控制语言来编写事务处理程序。2原理分析(1) 事务的概念事务(transaction)是一组单一逻辑工作单元的操作集合，是用户采用高级数据操纵语言或编程语言书写的用户程序，并由事务开始
2、begin transaction 和事务结束 end transaction 来界定全体操作的集合。(2) 事务的性质数据库管理系统为了实现数据库系统的完整性，事务的 ACID 性质是数据库事务处理的基础，它要求事务具有以下的性质：原子性（atomicity）：要求事务的全部操作要么在数据库中全部正确的反映出来要么全部不反映。一致性(consistency): 数据库中数据不因事务的执行而受到破坏，事务执行的结果应当使得数据库由一种一致性达到另一种新的一致性。数据的一致性保证数据库的完整性。隔离性(isolation): 事务的并发执行与这些事务单独执行的结果一样。也就是说，在多个事务并发执
3、行时，各个事务不必关心其它事务的执行，如同在单个用户环境下执行一样。事务的隔离性是事务并发控制技术的基础。持久性(durability)：事务对数据库的更新应永久地反映在数据库中。也就是说，一个事务一旦完成其全部操作之后，它对数据库所有更新操作的结果将在数据库中永久存在，即使以后发生故障也应保留这个事务的执行结果。持久性的意义在于保证数据库具有可恢复性。(3) 事务的控制事务的操作可由事务开始、事务读写、事务提交、事务回滚若干个基本操作组成，SQL Server 提供事务控制语法，来将 SQL Server 语句集合分组后形成单个的逻辑工作单元，每个单元都是一个独立的事务。Comment zh
4、hx1: 已将该例子进行更新Comment I2: 将此处增加的表结构增加到 school数据库中，请编写创建结构和初始数据的代码zheng.hx回复：此处的表结构及数据在原先数据库已经存在表 4.1.1 事务控制语句语法及含义事务控制语句语法 事务控制语句的含义BEGIN TRAN 表示事务开始执行COMMIT TRAN 表示事务完成所有数据操作，同时保存操作结果，它标志着事务的成功完成ROLLBACK TRAN 表示事务未完成所有数据操作，重新返回到事务开始，它标志着事务的撤销SAVE TRAN 表示完成部分事务，同时撤消事务的其他部分在事务控制中常常还需要通过检测两个全局变量：ERROR
5、，TRANCOUNT 来检测事务的状态。全局变量ERROR 记录任何 Transact SQL语句中的最近错误。如果语句成功执行，变量值为 0；如果语句执行失败，变量值不为 0。在事务定义处理时，往往需要检查ERROR来判断语句执行是否成功。如果没有成功，则需要使用 ROLLBACK TRAN语句来撤消事务。全局变量TRANCOUNT 记录 SQL Server当前等待提交的事务数，如果没有等待提交的事务数，全局变量TRANCOUNT 的值为 0。(4) 事务的类型对数据库的访问是建立在对数据“读”和“写”两个操作之上的，因此，一般事务中涉及到数据操作主要是由“读”与“写”语句组成，而当事务仅
6、由读语句组成时，事务的最终提交就会变得十分简单。因此，有时可以将事务分成只读型和读写型两种。只读型（Read Only）此时，事务对数据库的操作只能是读语句，这种操作将数据 X由数据库中取出读到内存的缓冲区中。定义此类型即表示随后的事务均是只读型，直到新的类型定义出现为止。读写型（Read/Write）此时，事务对数据库可以做读与写的操作，定义此类型后，表示随后的事务均为读/写型，直到新的类型定义出现为止。此类操作可以缺省。上述两种类型可以用下面的 SQL语句定义：SET TRANSACTION READ ONLYSET TRANSACTION READ WRITE3实验内容事务编程是数据库应
7、用系统中经常要用到的技术，通过使用事务控制语言和 SQL语句实现各种事务操作。4实验步骤假设对于某数据库用户在银行有两个帐号，分别为帐号和帐号，现在需要从帐号转 1000元到帐号，编写事务处理程序，实现这一操作。假设学校将学生的银行卡和校园卡进行了绑定，允许学生直接从银行卡转账到校园卡中。假设某学号为 05212222的学生需要从银行卡中转账 100元到校园卡中，编写事务处理程序，实现这一操作。要求：(1) 采用隐式事务方式来实现事务编程。(2) 采用显示显式用户定义事务的方式来实现事务编程。(3) 事务与批命令。(4) 嵌套事务的编程。(5 ) 在存储过程、触发器中使用事务编程。(6) 命名
8、事务与事务保存点。分析与解答：(1) AutoCommit 事务是 SQL Server 默认事务方式。它指出每条 SQL 语句都构成事务，隐含事务的开始与结束控制点。见示例代码 4.1.1 示例代码 4.1.1 实现从银行卡中转账100 元到校园卡的功能。如果在执行语句时遇到错误，则撤消操作，否则提交并保存操作的结果。代码 4.1.1思考一下，这样的事务编程是否会造成数据库状态的不一致呢？由于上面的代码中采取的隐式事务方法，如果对该学生的帐户 A 银行卡数据更新成功，由会自动向数据库提交。假若后来对帐户 B 该学生校园卡的数据的更新末能成功，则就造成转帐失败，但前一操作已经提交，无法还原。这
9、就可能会造成对数据库状态与事实语义不一致。针对这一问题，需要设法将这若干条 SQL 语句组合成一个独立的事务，这样才能保证各个操作步骤要么同时成功，要么一起失败。这就需要操作步骤 2 中所提出的定义显式事务方式来处理事务。(2) 为了完全控制事务并定义多个操作步骤组成的逻辑工作单元，可以采用显示显式用户定义事务的方式来实现用户期望的逻辑操作，见代码 4.1.2。代码 4.1.2(3) 批处理是由一条或多条 Transact SQL 语句或命令组成的，它能够成组的运行，用于向 SQL Server 提交成组的 Transact SQL 的语句组，由 Go 语句来终止语句组。批处理经过整体编译一次
10、成为一个执行计划，并一次将整个执行计划执行完毕。注意除非批命令没有固有的事务性质量，除显示显式的定义由几个语句构成的单个事务，否则批命令中的每条语句都是一个互相独立的事务，每条语句单独完成或者失败，而且批命令中的一个事务失败，不会影响其他语句的执行。利用查询分析器中执行代码 4.1.3：在查询分析器中输入代码 4.1.3：Update courses set hour=96 where cid=10001 Insert teachers values(1234567890,MY,MYZSU.EDU.CN,3000)Select top 10 * from teachers (HoldLock)
11、Go代码 4.1.3打开 SQL Server Profiler，新建跟踪，使用 standard 标准模板。此时 SQL Server Profiler 中会把 SQL Server 中的事件记录下来。执行代码 4.1.3，结果如 4.11 所示。显示 SQL BatchStarting 和 SQL BatchCompleted 语句都分别只有一条，说明批处理是一个完整的执行计划，并且要将整个执行计划执行完毕。并打开事件探查器以标准的 SQLProfilerStandard模板来记录 SQL Server 所发生的事件，得到图 4.1.1 的结果，显示只有一条 SQL BatchComple
12、ted 语句，说明批处理是一个完整的执行计划，并且要将整个执行计划执行完毕。Comment zhhx3: 不成立。没有 Go也会得到一样的结果。除非每次选择一条语句执行，执行三次，才会出现不同的结果。是吗？有待验证！图 4.1.1 批处理操作跟踪相反，如果没有 GO语句，读者也可以通过事件探查器来观察一下，结果是每个语句单独执行。(4) 嵌套事务主要是为了支持存储过程中的一些事务，这些事务可以从事务中已有的进程中调用，也可以从没有活动的事务进程中调用。嵌套事务对于 COMMIT TRANSACTION语句的每个调用都对应于最后执行的 BEGIN TRANSACTION语句，也就是最内层的事务。
13、读者执行事务嵌套的实例代码 4.1.4，执行结果如图 4.1.2。 。SELECT BEFORE TRANSACTION: AS HINT,TRANCOUNT AS TRANACTIONCOUNTBEGIN TRANSELECT THE FIRST TRANSACTION STARTS: AS HINT,TRANCOUNT AS TRANACTIONCOUNTSELECT TOP 3 * FROM CHOICESBEGIN TRANSELECT THE SECOND TRANSACTION STARTS: AS HINT,TRANCOUNT AS TRANACTIONCOUNTCOMMIT T
14、RANSELECT THE SECOND TRANSACTION COMMITS AS HINT,TRANCOUNT AS TRANACTIONCOUNTROLLBACK TRANSELECT THE FIRST TRANSACTION ROLL BACK AS HINT,TRANCOUNT AS TRANACTIONCOUNT代码 4.1.4图 4.1.2(5) 触发器是一种特殊类型的存储过程，主要用于完成数据库中对象的完整性。在表中进行数据修改时自动执行，触发器被视为执行数据修改事务的一部分，与数据修改语句在同一事务空间中执行。由于触发器已经在事务情境中操作，因此事务中要的事务控制语句只有
15、 ROLLBACK 或者 SAVE TRAN，用户不需要发出 BEGIN TRAN。代码 4.1.5 为Courses 的删除操作创建一个触发器，然后执行一个删除操作，观察事务数目的变化。在删除操作执行的过程中，触发器得到执行，而且事务的数目为 1，这就验证了触发器事务是数据修改事务的一部分。CREATE TRIGGER TD_COURSE ON COURSESFOR DELETEAS DECLARE INFO VARCHAR(255)SELECT INFO=触发器中的事务数据为：+CONVERT(VARCHAR(2),TRANCOUNT)PRINT INFORETURN代码 4.1.5执行以
16、下代码：PRINT 删除操作以前触发器中的事务数为: + CONVERT(VARCHAR(2),TRANCOUNT);DELETE FROM COURSESWHERE CID=10052PRINT 删除操作之后触发器中的事务数为: + CONVERT(VARCHAR(2),TRANCOUNT);所得结果图 4.1.3 所示。图 41.3 同样可以从事务中调用存储过程，也可以在存储过程中启动事务，而且这是经常在数据库开发过程中应用到的，因为在存储过程中使用事务，可以提高数据库操作的效率，可以方便维护。在企业管理器中创建存储过程图 4.1.4 示。CREATE PROCEDURE INSERTCO
17、URSEINFOcourseid char(10),coursename varchar(30),hour int,returnString varchar(100)ASBEGIN TRANIF EXISTS(SELECT CID FROM COURSES WHERE CID=COURSEID)BEGINSELECT returnString=课程信息已经存在GOTO ONERROREND-新增课程信息INSERT INTO COURSES VALUES(courseid,courseName,hour)IF ERROR0 BEGINWAITFOR DELAY 00:00:20- PRINT
18、模拟实现不可重复读SELECT * FROM TEACHERS WHERE TID=200003125END代码 4.3.2连接 2 执行的结果图 4.3.1：图 4.3.1从结果中，我们可以看出，事务 2 第一次读到的数据 4200 是事务 1 没有提交的数据。当事务 2 第二次去读数据时，事务 1 回滚了，所以读到的数据与第一次读到的数据是不一致的。在这儿，第一次发生了数据脏读，第二次发生了不可重复读。发生“脏读”的原因就是在事务 1 的执行过程没有和事务 2 的执行过程相互隔离，导致事务 2 读取了事务 1 没有确定提交的数据，在实际应用的情况下，这种情况应当避免。(2) 在查询分析器中
19、新建两个连接，在连接 1 中执行代码，用于在事务中实现两次重复相同的查询，在连接 2 中更新记录。连接 1 中执行代码 4.3.3 为：SET TRANSACTION ISOLATION LEVEL READ COMMITTED- 初始状态BEGIN TRANSELECT * FROM TEACHERS WHERE TID=200003125IF ROWCOUNT0 BEGINWAITFOR DELAY 00:00:20- PRINT 模拟实现不可重复读SELECT * FROM TEACHERS WHERE TID=200003125ENDROLLBACK TRAN代码 4.3.5在连接 2中执行代码 4.3.6：SET TRANSACTION ISOLATION LEVEL REPEATABLE READUPDATE TEACHERS SET SALARY=4200 4500 WHERE TID=200003125代码 4.3.6连接 1中执行的结果为图 4.3.3：
