代码C16-事务处理transaction.doc

第16章 事务处理第16章 事务处理16.1 自动事务处理USE AdventureWorks;GO -第1个批CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3);GO -第2个批INSERT INTO TestBatch VALUES (1, aaa);INSERT INTO TestBatch VALUES (2, bbb);INSERT INTO TestBatch VALUSE (3, ccc); - 语法有错误GO -第3个批SELECT * FROM TestBatch; - Returns no rows.GO -第4个批USE AdventureWorks;GOCREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3);GOINSERT INTO TestBatch VALUES (1, aaa);INSERT INTO TestBatch VALUES (2, bbb);INSERT INTO TestBatch VALUES (1, ccc); - 重复键错误.GOSELECT * FROM TestBatch; - 返回1和2行USE AdventureWorks;GOCREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3);GOINSERT INTO TestBatch VALUES (1, aaa);INSERT INTO TestBatch VALUES (2, bbb);INSERT INTO TestBch VALUES (3, ccc); - 表名称错误GOSELECT * FROM TestBatch; - 返回1和2行16.2 显式事务处理BEGIN TRY BEGIN TRANSACTION - 开始事务 INSERT MyTable VALUES(3,ASDF) ; - 插入数据 COMMIT TRANSACTION - 提交插入操作END TRY - 如果上面的TRY块中出现错误，将执行下面CATCH块中的语句BEGIN CATCH SELECT ERROR_NUMBER() AS ERRORNUMBER ; - ERROR_NUMBER函数用于返回错误编号 ROLLBACK TRANSACTION -回滚事务操作END CATCH16.3 隐式事务处理USE AdventureWorks;GOCREATE TABLE ImplicitTran (Cola int PRIMARY KEY, Colb char(3) NOT NULL);BEGIN TRY INSERT INTO ImplicitTran VALUES (1, aaa); INSERT INTO ImplicitTran VALUES (1, bbb); - 提交事务 COMMIT TRANSACTION;END TRY- 如果上面的TRY块中出现错误，将执行下面CATCH块中的语句BEGIN CATCH ROLLBACK TRANSACTION; -回滚事务操作END CATCHSELECT * FROM ImplicitTran;DELETE FROM ImplicitTran;SET IMPLICIT_TRANSACTIONS ON; - 启动隐式事务处理GOBEGIN TRY INSERT INTO ImplicitTran VALUES (1, aaa); INSERT INTO ImplicitTran VALUES (1, bbb); - 提交事务 COMMIT TRANSACTION;END TRY- 如果上面的TRY块中出现错误，将执行下面CATCH块中的语句BEGIN CATCH ROLLBACK TRANSACTION; -回滚事务操作END CATCHSET IMPLICIT_TRANSACTIONS OFF; -关闭隐式事务处理SELECT * FROM ImplicitTran;16.4 使用嵌套事务USE AdventureWorks;GOCREATE TABLE TestTrans(Cola INT PRIMARY KEY, Colb CHAR(3) NOT NULL);GOCREATE PROCEDURE TransProc PriKey INT, CharCol CHAR(3)ASBEGIN BEGIN TRANSACTION InProc INSERT INTO TestTrans VALUES (PriKey, CharCol) INSERT INTO TestTrans VALUES (PriKey + 1, CharCol) COMMIT TRANSACTION InProc;ENDGO- 开始一个事务并执行TransProc存储过程BEGIN TRANSACTION OutOfProc;EXEC TransProc 1, aaa;- 回滚外部事务，也将回滚TransProc中的嵌套事务ROLLBACK TRANSACTION OutOfProc;GOEXECUTE TransProc 3,bbb;GO- 下面的SELECT语句显示表中仅有Cola列为3和4的行，而没有上面提交1和2行。- 这说明由第一个EXECUTE语句执行的提交动作被外部的回滚操作覆盖了，并没有- 执行。因为对于已实际提交的操作是无法回滚的SELECT * FROM TestTrans;USE AdventureWorks;GOCREATE TABLE TestTrans(Cola INT PRIMARY KEY, Colb CHAR(3) NOT NULL);GOCREATE PROCEDURE TransProc PriKey INT, CharCol CHAR(3)ASBEGIN BEGIN TRY BEGIN TRANSACTION InProc -开始InProc事务 INSERT INTO TestTrans VALUES (PriKey, CharCol) COMMIT TRANSACTION InProc; -提交事务 END TRY - 在出现提交错误时回滚操作，由于不能引用内部事务名称，在需要回滚时 - SQL Server将提示找不到InProc事务名称 BEGIN CATCH ROLLBACK TRANSACTION InProc END CATCHENDGO- 插入一行EXEC TransProc 1, aaa;GO- 开始一个事务并执行TransProc存储过程BEGIN TRANSACTION OutOfProc;GO- 再次执行下面的EXEC语句会造成TestTrans表中出现重复键，因此引发错误，这会- 导致执行TransProc存储过程中的CATCH部分，进行回滚操作。此时，SQL Server- 提示无法无法回滚InProc，找不到该名称的事务或保存点。全部提示信息如下：/* (0 行受影响)消息 6401，级别 16，状态 1，过程 TransProc，第 11 行无法回滚 InProc。找不到该名称的事务或保存点。消息 266，级别 16，状态 2，过程 TransProc，第 0 行EXECUTE 后的事务计数指示缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。上一计数 = 1，当前计数 = 2。*/EXEC TransProc 1, aaa;GO- 下面的语句用于回滚所有操作，退出嵌套事务ROLLBACK TRANSACTION OutOfProc; CREATE PROCEDURE TransProc PriKey INT, CharCol CHAR(3)ASBEGIN BEGIN TRY BEGIN TRANSACTION InProc SAVE TRANSACTION ttt - 设置保存点 INSERT INTO TestTrans VALUES (PriKey, CharCol) COMMIT TRANSACTION InProc; END TRY BEGIN CATCH ROLLBACK TRANSACTION ttt - 回滚到保存点 - 由于仅是回滚到保存点，所以仍旧需要使用COMMIT语句提交并结束事务 - 否则无法退出嵌套。有关嵌套层次的信息，参考后面的TRANCOUNT函数介绍 COMMIT TRANSACTION InProc END CATCHEND16.5 使用事务保存点SET NOCOUNT OFF;GOUSE AdventureWorks;GOCREATE TABLE InvCtrl (WhrhousID int, PartNmbr int, QtyInStk int, ReordrPt int, CONSTRAINT InvPK PRIMARY KEY (WhrhousID, PartNmbr), CONSTRAINT QtyStkCheck CHECK (QtyInStk 0) );GOCREATE PROCEDURE OrderStock WhrhousID int, PartNmbr int, OrderQty intASDECLARE ErrorVar int;BEGIN TRY BEGIN TRANSACTION InProc; SAVE TRANSACTION StkOrdTrn; UPDATE InvCtrl SET QtyInStk = QtyInStk - OrderQty WHERE WhrhousID = WhrhousID AND PartNmbr = PartNmbr; COMMIT TRANSACTION InProc; RETURN 0;END TRYBEGIN CATCH SELECT ErrorVar = error; IF (ErrorVar = 547) BEGIN ROLLBACK TRANSACTION StkOrdTrn; COMMIT TRANSACTION InProc RETURN (SELECT QtyI