事务处理控制.docx

1、事务处理控制目标如果你是一个临时用户只需要偶而使用SQL 从数据库获得数据的话那么前十天的的主题已经为你提供了足够的内容但是如果你想开发可以在使用数据库系统下运行的专业应用程序这在当前在很普遍的那么你在今后四天中讲到的内容事务控制安全内嵌SQL 语句数据库过程将会对你有很大的帮助我们先从事务控制开始到今天的结束我们将学会以下内容?基本的事务控制?如何确认或终止某一项事务?Sybase 与Oracle 在事务处理上的不同之处注在今天的例子中我们使用PERSONAL ORACLE 7和SYBASE SQL SERVER 对于你所使用的解释器请查看相应的帮助文档以找出它们的不同之处事务控制事务控制或

2、者说事务处理是指关系数据库系统执行数据库事务的能力事务是指在逻辑上必须完成的一命令序列的单位单元工作期是指事务的开始和结束时期如果在事务中产生的错误那么整个过程可以根据需要被终止如果每一件事都是正确的那么结果将会被保存到数据库中日后你也许会运行其于网络的多用户应用程序客户/服务环境就是为它而设计的传SQL 21 日自学通(V1.0) 翻译人笨猪224统上的服务器例如数据库服务器支持多个与它连接的工作站与其它技术一样新特性提高了数据库的复杂程度下边的几段描述了一个银行所使用的应用程序银行应用程序假定你受雇于联邦银行并负责为他们设计一个支票管理系统你已经设计了一个非常完美的数据库并且经常测试检验证

3、明是正确无误的你在应用程序中调用它以后你从账号中支取了20 元并进行验证数据库中确实已经少了20 元你又从帐号中存入了50.25元并进行验证结果也与所期望的相同于是你骄傲在告诉你的老板系统可以运行了几台计算机接入了程序并开始工作几分钟以后你注意到了一个你没有预见的问题一个出纳员向帐号中存入了一张支票而另一个出纳则从相同的帐号中提出了一部分钱在分钟之内由于多用户的同时操作就导致的帐目无法平衡很不幸由于他们之间互相进行更新和写入操作你的应用程序很快就因为过负荷而断线我们假定出现这个问题的数据库名字叫CHECKING 它有两个表其内容如下所示表11.1Name Address City State

4、Zip Customer_IDBill Turner 725 N. Deal Parkway Washington DC 20085 1John Keith 1220 Via De Luna Dr. Jacksonville FL 33581 2Mary Rosenberg 482 Wannamaker Avenue Williamsburg VA 23478 3David Blanken 405 N. Davis Highway Greenville SC 29652 4Rebecca Little 7753 Woods Lane Houston TX 38764 5表11.2Average

5、_Bal Curr_Bal Account_ID1298.53 854.22 15427.22 6015.96 2211.25 190.01 373.79 25.87 41285.90 1473.75 51234.56 1543.67 6345.25 348.03 7假定你的应用程序为BILL Turner 运行了SELECT 查询并得到如下结果OUTPUTSQL 21 日自学通(V1.0) 翻译人笨猪225NAME Bill TurnerADDRESS 725 N. Deal ParkwayCITY WashingtonSTATE DCZIP 20085CUSTOMER_ID 1当返回数据的

6、时候另外一个用户连接到了数据库并更新了BILL Turner 的住址信息INPUTSQL UPDATE CUSTOMERS SET Address =11741 Kingstowne RoadWHERE Name = Bill Turner你现在看到了如果在你执行SELECT 语句当中出现的更新操作的话那么你所得到的结果将是不正确的如果你的应用程序可以生成一个信件给Bill Turner 那么由于地址是错误的如果信已经发送了那你是不能对地址进行修改的但是如果你使用了事务处理机制那么你就可以对检测到错误的数据进行修改你所进行的所有操作也都可以撤消开始事务处理事务处理在执行上是非常简单的你需要检查

7、你所执行的语法是Oracle RDBMS SQL语法还是Sybase SQL Server SQL 语法所有支持事务处理的系统都必须以一种准确的语法来告诉系统一项事务是如何开始的不要忘记事务处理只是工作的逻辑分组它有自己的开始和结束在使用PERSONALORACLE7 时它的语法形式如下SYNTAXSET TRANSACTION READ ONLY | USE ROLLBACK SEGMENT segmentSQL 标准要求每一种数据库的SQL 解释器都必须运支持语句级的读一致这也就是说当某一条语句运行的时候数据必需保持不变但是在许多情况下在一个工作过程中必须要求数据保持有效而不仅仅是对单个语

8、句ORACLE 允许用户用SET TRANSACTION来指定事务的开始如果你想检查BILL TUNER 的信息并且要保证数据在这之中是不能改变的那么你可以使用如下语句INPUTSQL 21 日自学通(V1.0) 翻译人笨猪226SQL SET TRANSACTION READ ONLYSQL SELECT * FROM CUSTOMERSWHERE NAME = Bill Turner;SQL COMMIT我们将在今天的晚些时候来讨论COMMIT 语句这里的SET TRANSACTION READONLY 允许你锁定一个记录集直到事务结束你可以在下列语句中使用READ ONLY 选项SELE

9、CTLOCK TABLESET ROLEALTER SESSIONALTER SYSTEM选项USE ROLLBACK SEGMENT 告诉ORACLE 数据库提供数据回溯的存储空间段这一选项是ORACLE 对标准的SQL 的扩展如果需要维护你的数据库请参见ORACLE 的帮助文档以获得更多的帮助信息SQL Servers Transact-SQL 语言用下边的方法来实现了开始事务处理的命令SYNTAXbegin transaction | tran transaction_name它的实现方法与ORACLE 的有一些不同SYBASE 不允许你指定READ ONLY 选项但是SYBASE 允许

10、你给出事务处理的名字从最早的事务到最近发生的事务处理都可以一次退回INPUT1 begin transaction new_account2 insert CUSTOMERS values (Izetta Parsons, 1285 Pineapple Highway, Greenville, AL32854, 6)3 if exists(select * from CUSTOMERS where Name = Izetta Parsons)4 begin5 begin transaction6 insert BALANCES values(1250.76, 1431.26, 8)7 end8

11、 elseSQL 21 日自学通(V1.0) 翻译人笨猪2279 rollback transaction10 if exists(select * from BALANCES where Account_ID = 8)11 begin12 begin transaction13 insert ACCOUNTS values(8, 6)14 end15 else16 rollback transaction17 if exists (select * from ACCOUNTS where Account_ID = 8 and Customer_ID = 6)18 commit transac

12、tion19 else20 rollback transaction21 go现在请不要担心ROLLBACK TRANSACTION 和COMMIT TRANSACTION 语句重要的问题是这是一个内嵌的事务处理或者说是事务处理之中还有事务处理注意最开始的事务处理在第1 行之后是插入语句你检查了插入确实已经执行了以后第二个事务处理在第5 行开始这种在事务之中的事务在术语上称为内嵌事务有一些数据库支持AUTOCOMMIT 选项它可以在SET 命令中使用如下例SET AUTOCOMMIT ON | OFF默认情况上SET AUTOCOMMIT ON 命令在启动时是自动运行的它告诉SQL 自动确认你

13、所运行的所有的语句如果你不想让这个命令自动运行那么请将它的参数设为NOSET AUTOCOMMIT OFF注请检查你的数据库文档确认在你的数据库系统中一项事务处理是如何开始的结束事务处理在ORACLE 语法中结束事务处理语句的语法如下SYNTAXCOMMIT WORK COMMENT textSQL 21 日自学通(V1.0) 翻译人笨猪228| FORCE text , integer ;它的命令语法与Sybase 的语法是相同的语法COMMIT (TRANSACTION | TRAN | WORK) (TRANSACTION_NAME)COMMIT 命令将保存在一项事务中所进行的所有的改变

14、在开始一项事务处理之前要先运行COMMIT 命令以确保在之前没有事务未被确认在下边的例子中如果COMMIT 没有收到任何系统错误的情况下它将会执行确认INPUTSQL COMMIT;SQL SET TRANSACTION READ ONLY;SQL SELECT * FROM CUSTOMERSWHERE NAME = Bill Turner;-Do Other Operations-SQL COMMIT;在ORACLE 中COMMIT 语句的使用方法如下INPUTSQL SET TRANSACTION;SQL INSERT INTO CUSTOMERS VALUES(John MacDowe

15、ll, 2000 Lake Lunge Road, Chicago, IL, 42854, 7);SQL COMMIT;SQL SELECT * FROM CUSTOMERS;CUSTOMER 表的内容如下Name Address City State Zip Customer_IDBill Turner 725 N. Deal Parkway Washington DC 20085 1John Keith 1220 Via De Luna Dr. Jacksonville FL 33581 2Mary Rosenberg 482 Wannamaker Avenue Williamsburg

16、VA 23478 3David Blanken 405 N. Davis Highway Greenville SC 29652 4Rebecca Little 7753 Woods Lane Houston TX 38764 5Izetta Parsons 1285 Pineapple Highway Greenville AL 32854 6John MacDowell 2000 Lake Lunge Road Chicago IL 42854 7而Sybase SQL 使用COMMIT 的语法方式如下SQL 21 日自学通(V1.0) 翻译人笨猪229INPUT1begin transa

17、ction2insert into CUSTOMERS values(John MacDowell, 2000 Lake Lunge Road, Chicago, IL, 42854, 7)3commit transaction4go1select * from CUSTOMERS2goName Address City State Zip Customer_IDBill Turner 725 N. Deal Parkway Washington DC 20085 1John Keith 1220 Via De Luna Dr. Jacksonville FL 33581 2Mary Rose

18、nberg 482 Wannamaker Avenue Williamsburg VA 23478 3David Blanken 405 N. Davis Highway Greenville SC 29652 4Rebecca Little 7753 Woods Lane Houston TX 38764 5Izetta Parsons 1285 Pineapple Highway Greenville AL 32854 6John MacDowell 2000 Lake Lunge Road Chicago IL 42854 7上边的语句完成了与ORACLE7 相同的功能但是在使用COMM

19、IT 确认事务处理之前你应该确保在该事务中的工作是正确无误的注COMMIT WORD 命令与COMMIT 命令的作用是相同的或Sybase 中的COMMITTRANSACTION 它与ANSI SQL 的语法一样的简单切记COMMIT 语句一定要与之前的SET TRANSCATION 或BEGIN TRANSCATION语句一致注意在下边的语句中你将会收到错误信息Oracle SQLINPUTSQL INSERT INTO BALANCES values (18765.42, 19073.06, 8);SQL COMMIT WORK;Sybase SQLINPUT1 insert into B

20、ALANCES values (18765.42, 19073.06, 8)2 commit workSQL 21 日自学通(V1.0) 翻译人笨猪230取消事务处理在一个事务处理的过程中常常会运行一些错误检查以确认在过程中是否语句是运行成功你可以使用ROLLBACK 语句来撤消事务中所做的每一项工作即便工作是成功的你也可以撤消但是这必须是在COMMIT 之前ROLLBACK 语句必须在一个事务之中运行它可以一直撤消到事务的开始也就是说数据库会一直返回到事务处理刚开始的状态在ORACLE 7 中它的语法形式如下SYNTAXROLLBACK WORK TO SAVEPOINT savepoint

21、| FORCE text 如你所见该命令可以设置事务的SAVEPOINT 我们将在今天的晚些时候来讨论这项技术Sybase Transact-SQLs 的ROLLBACK语句与COMMIT 语句非常相似SYNTAXrollback transaction | tran | work transaction_name | savepoint_name一个ORACLE 的命令序列如下INPUTSQL SET TRANSACTION;SQL INSERT INTO CUSTOMERS VALUES(Bubba MacDowell, 2222 Blue Lake Way, Austin, TX, 39

22、874, 8);SQL ROLLBACK;SQL SELECT * FROM CUSTOMERS;Name Address City State Zip Customer_IDBill Turner 725 N. Deal Parkway Washington DC 20085 1John Keith 1220 Via De Luna Dr. Jacksonville FL 33581 2Mary Rosenberg 482 Wannamaker Avenue Williamsburg VA 23478 3David Blanken 405 N. Davis Highway Greenvill

23、e SC 29652 4Rebecca Little 7753 Woods Lane Houston TX 38764 5Izetta Parsons 1285 Pineapple Highway Greenville AL 32854 6John MacDowell 2000 Lake Lunge Road Chicago IL 42854 7SQL 21 日自学通(V1.0) 翻译人笨猪231而A Sybase SQL 的命令序列则如下INPUT1 begin transaction2 insert into CUSTOMERS values(Bubba MacDowell, 2222 B

24、lue Lake Way, Austin, TX, 39874, 8)3 rollback transaction4 go1 SELECT * FROM CUSTOMERS2 goName Address City State Zip Customer_IDBill Turner 725 N. Deal Parkway Washington DC 20085 1John Keith 1220 Via De Luna Dr. Jacksonville FL 33581 2Mary Rosenberg 482 Wannamaker Avenue Williamsburg VA 23478 3Dav

25、id Blanken 405 N. Davis Highway Greenville SC 29652 4Rebecca Little 7753 Woods Lane Houston TX 38764 5Izetta Parsons 1285 Pineapple Highway Greenville AL 32854 6John MacDowell 2000 Lake Lunge Road Chicago IL 42854 7你也看到了由于使用了ROLLBACK 命令撤消了INSERT 命令新的记录并没有被加入到表中如果你写了一个图形用户界面的应用程序比如MICRO WINDOWS 你可以做一

26、个数据库查询对话框以便让用户在其中输入数值如果用户按下了确定按钮那么数据库将会保存所做的改动如果用户按下了取消按钮那么所有的更改就会被取消显然这种情况给予了你使用事务处理的机会注下边的代码给出的ORACLE SQL 中的使用方法注意这里有SQL并且有行号在随后给出的Sybase SQL syntax 中则没有SQL提示符当对话框载入后这些SQL 语句将会运行INPUTSQL SET TRANSACTION;SQL SELECT CUSTOMERS.NAME, BALANCES.CURR_BAL, BALANCES.ACCOUNT_ID2 FROM CUSTOMERS, BALANCES3 WH

27、ERE CUSTOMERS.NAME = Rebecca LittleSQL 21 日自学通(V1.0) 翻_译人笨猪2324 AND CUSTOMERS.CUSTOMER_ID = BALANCES.ACCOUNT_ID;该对话框允许用户更改当前的结算账号所以你需要将该数据返回给数据库当按下OK 按钮以后UPDATE将会运行INPUTSQL UPDATE BALANCES SET CURR_BAL = new-value WHERE ACCOUNT_ID = 6;SQL COMMIT如果用户按下了CANCEL 那么将会运行ROLLBACK命令INPUTSQL ROLLBACK当该对话框在Sy

28、base SQL 中被载入以后将会运行下边的语句INPUT1 begin transaction2 select CUSTOMERS.Name, BALANCES.Curr_Bal, BALANCES.Account_ID3 from CUSTOMERS, BALANCES4 where CUSTOMERS.Name = Rebecca Little5 and CUSTOMERS.Customer_ID = BALANCES.Account_ID6 go该对话框允许用户改变当前的结算账号当你将该数据返回给数据库以后并按下OK按钮时UPDATE语句将会运行INPUT1 update BALANC

29、ES set Curr_BAL = new-value WHERE Account_ID = 62 commit transaction3 go如果用户选择了CANCEL 按钮那么将会执行ROLLBACK的语句INPUT1 rollback transaction2 goROLLBACK 语句将会终止整个事务当存在嵌套事务时ROLLBACK 将会终止掉全部事务系统将会返回到事务开始的最初状态如果当前没有活动的事务时ROLLBACK 或COMMIT 语句将不会对数据库产生任何SQL 21 日自学通(V1.0) 翻译人笨猪233作用你可以认为这是一个无效的命令在COMMIT 语句运行以后在事务中的

30、所有动作都会得到确认这时在使用ROLLBACK命令就太晚了在事务中使用保存点在事务中使用ROLLBACK 可以取消整个的事务但是你也可以在你的事务当中使用语句进行部分地确认在Sybase 和Oracle 中都允许你在当前事务中设一个保存点从这一点开始如果你使用了ROLLBACK 命令那么系统将会回到保存点时的状态而在保存点之前的语句将会得到确认在ORACLE 中创建一个保存点的语法格式如下SYNTAXSAVEPOINT savepoint_name;在SYBASE 中创建保存点的语法格式如下SYNTAXsave transaction savepoint_name下边是使用ORACLE 语法的例子INPUTSQL SET TRANSACTIONSQL UPDATE BALANCES SET CURR_BAL = 25000 WHERE ACCOUNT_ID = 5SQL SAVEPOINT save_itSQL DELETE FROM BALANCES WHERE ACCOUNT_ID = 5SQL ROLLBACK