SQL Transcation的一些总结分享

CREATE PROCEDURE SPMultiDataToUserInfo
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
–SET NOCOUNT ON;
— Hard code inserted data.
INSERT INTO UserInfo VALUES(‘Cris’, 1);
EXEC SPAddDataToUserInfo
INSERT INTO UserInfo VALUES(‘Ada’, 32);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
GO

我们知道存储过程SPAddDataToUserInfo会发生异常，它会回滚事务（ROLLBACK），但SPMultiDataToUserInfo是发生回滚还是继续插入数据呢？

图6 执行存储过程消息

图7 UserInfo表中数据
在插入的过程发生了异常，检查UserInfo表数据并没有插入表中，这符合我们设计的意图，但我们发现异常不仅仅是产生于插入的数据超出了数值的范围，还包含事务计数异常。
由于定义了两个存储过程，而且SPAddDataToUserInfo内嵌在SPMultiDataToUserInfo中，在执行这两个存储过程中，它们都发生了异常并且进行事务回滚（因为User为空）。
由于插入的数据超出了数值的范围的异常是我们特意引起的，而事务计数异常这是预期之外的异常。
接下来让我们看一下究竟是什么原因引起了该异常，这里我们通过输出Transactions计数来查看问题所在。

— =============================================
— Author: JKhuang
— Create date: 12/8/2011
— Description: Inserts data
— =============================================
Alter PROCEDURE SPAddDataToUserInfo
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
PRINT ‘In [SPAddDataToUserInfo] Transactions: ‘ + Convert(varchar, @@TRANCOUNT);
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
–SET NOCOUNT ON;
— Hard code inserted data.
INSERT INTO UserInfo VALUES(‘JKhuang’, 8);
INSERT INTO UserInfo VALUES(‘Jackson’, 20111111);
INSERT INTO UserInfo VALUES(‘JKRush’, 23);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT ‘Error in [SPAddDataToUserInfo]: ‘ + ERROR_MESSAGE();
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE();
PRINT ‘Rolled back successful Transactions: ‘ + Convert(varchar, @@TRANCOUNT);
END CATCH
END
GO
— =============================================
— Author: JKhuang
— Create date: 12/8/2011
— Description: Invokes store procedure to insert data.