SQL Server 2005/2008/2012中事务回滚的一个充分条件

SQL Server 2008中SQL应用系列--目录索引

　　在SQL Server 2000中，我们一般使用RaiseError（http://msdn.microsoft.com/zh-cn/library/ms177497.aspx）来抛出错误交给应用程序来处理。看MSDN示例（http://msdn.microsoft.com/zh-cn/library/aa238452%28v=sql.80%29.aspx），自从SQL Server 2005集成Try…Catch功能以后，我们使用时更加灵活，到了SQL Server 2012，更推出了强大的THROW，处理错误显得更为精简。本文对此作一个小小的展示。

　　首先，我们假定两个基本表如下：

--创建两个测试表 IF NOT OBJECT_ID('Score') IS NULL DROP TABLE [Score] GO IF NOT OBJECT_ID('Student') IS NULL DROP TABLE [Student] GO CREATE TABLE Student (stuid int NOT NULL PRIMARY KEY, stuName Nvarchar(20) ) CREATE TABLE Score (stuid int NOT NULL REFERENCES Student(stuid),--外键 scoreValue int ) GO INSERT INTO Student VALUES (101,'胡一刀') INSERT INTO Student VALUES (102,'袁承志') INSERT INTO Student VALUES (103,'陈家洛') INSERT INTO student VALUES (104,'张三丰') GO SELECT * FROM Student /* stuid stuName 101 胡一刀 102 袁承志 103 陈家洛 104 张三丰 */

　　我们从一个最简单的例子入手：

例一：

/********* 调用运行时错误 ***************/ /********* 3w@live.cn 邀月***************/ SET XACT_ABORT OFF BEGIN TRAN INSERT INTO Score VALUES (101,80) INSERT INTO Score VALUES (102,87) INSERT INTO Score VALUES (107, 59) /* 外键错误 */ -----SELECT 1/0 /* 除数为0错误 */ INSERT INTO Score VALUES (103,100) INSERT INTO Score VALUES (104,99) COMMIT TRAN GO

　　先不看结果，我想问一下，该语句执行完毕后，Score表会插入几条记录？估计可能有人说是2条，有人说0条，也可能有人说4条。

　　实际上，我希望是0条，但结果是4条！　

/* (1 row(s) affected) (1 row(s) affected) Msg 547, Level 16, State 0, Line 5 The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Score__stuid__01D345B0". The conflict occurred in database "testDb2", table "dbo.Student", column 'stuid'. The statement has been terminated. (1 row(s) affected) (1 row(s) affected) */ SELECT * from Score /* stuid scoreValue 101 80 102 87 103 100 104 99 */

　　我对这个结果也有点惊讶，我希望它出错回滚，于是修改：

例二：

/********* 调用运行时错误 ***************/ /********* 3w@live.cn 邀月***************/ TRUNCATE table Score GO SET XACT_ABORT OFF BEGIN TRAN INSERT INTO Score VALUES (101,80) INSERT INTO Score VALUES (102,87) INSERT INTO Score VALUES (107, 59) /* 外键错误 */ ----SELECT 1/0 --INSERT INTO Score VALUES (103,100) --INSERT INTO Score VALUES (104,99) PRINT '@@ERROR是:'+cast(@@ERROR as nvarchar(10)) IF @@ERROR<>0 ROLLBACK TRAN ELSE COMMIT TRAN GO 　　我先提示一下大家，这个语句中的@@ERROR值是547，那么此时，Score表中有几条记录?

　　答案是2条！

　　可能有人开始摇头了，那么问题的关键在哪儿呢?对，就是这个“XACT_ABORT ”开关，查MSDN（http://msdn.microsoft.com/zh-cn/library/ms188792.aspx），

官方解释：它用于指定当 Transact-SQL 语句出现运行时错误时，SQL Server 是否自动回滚到当前事务。当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。当 SET XACT_ABORT 为 OFF 时，有时只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。 如果错误很严重，那么即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务。 OFF 是默认设置。编译错误（如语法错误）不受 SET XACT_ABORT 的影响。对于大多数 OLE DB 访问接口（包括 SQL Server），必须将隐式或显示事务中的数据修改语句中的 XACT_ABORT 设置为 ON。 唯一不需要该选项的情况是在提供程序支持嵌套事务时。

　　这里，红色的一句话是关键，那么“有时”究竟是指什么时候呢？查资料知：（http://msdn.microsoft.com/zh-cn/library/ms164086.aspx）

　　大致分为以下四个级别：

　　　　当等级SEVERITY为0-10时，为“信息性消息”，最轻。

　　　　当等级为11-16时，为“用户可以纠正的数据库引擎错误”。如除数为零，等级为16

　　　　当等级为17-19时，为“需要DBA注意的错误”。如内存不足、数据库引擎已到极限等。

　　　　当等级为20-25时，为“致命错误或系统问题”。如硬件或软件损坏、完整性问题、媒体故障等。

　　用户也可以自定义错误级别和类型。

　　根据以上解释，我们最保险的方式是：Set XACT_ABORT ON。

　　当然，使用Try…Catch在Set XACT_ABORT OFF时也能按照我们的意愿回滚。

例三：

/********* 使用Try Catch 构造一个错误记录 ***************/ /********* 3w@live.cn 邀月 ***************/ SET XACT_ABORT OFF BEGIN TRY BEGIN TRAN INSERT INTO Score VALUES (101,80) INSERT INTO Score VALUES (102,87) INSERT INTO Score VALUES (107, 59) /* 外键错误 */ INSERT INTO Score VALUES (103,100) INSERT INTO Score VALUES (104,99) COMMIT TRAN PRINT '事务提交' END TRY BEGIN CATCH ROLLBACK PRINT '事务回滚' --构造一个错误信息记录 SELECT ERROR_NUMBER() AS 错误号, ERROR_SEVERITY() AS 错误等级, ERROR_STATE() as 错误状态, DB_ID() as 数据库ID, DB_NAME() as 数据库名称, ERROR_MESSAGE() as 错误信息; END CATCH GO　　这个返回结果比较另类，它其实是一条拼凑起来的记录。

　　记录并没有新增，因为Catch到错误而事务回滚了。

　　使用RaiseError也可以把出错的信息抛给应用程序来处理。

例四：

/********* 使用RaiseError 提交一个错误信息***************/ /********* 3w@live.cn 邀月 ***************/ SET XACT_ABORT OFF BEGIN TRY BEGIN TRAN INSERT INTO Score VALUES (101,80) INSERT INTO Score VALUES (102,87) INSERT INTO Score VALUES (107, 59) /* 外键错误 */ INSERT INTO Score VALUES (103,100) INSERT INTO Score VALUES (104,99) COMMIT TRAN PRINT '事务提交' END TRY BEGIN CATCH ROLLBACK PRINT '事务回滚';--构造一个错误信息记录 DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH GO 　　或者直接使用Throw也能达到RaiseError同样的效果，而且这是微软推崇的方式：其官方解释为“ THROW 语句支持 SET XACT_ABORT，但 RAISERROR 不支持。 新应用程序应该改用 THROW，而不使用 RAISERROR。”其实，可能是微软在忽悠，因为，其实RaiseError也支持Set XACT_ABORT。

例五：

/********* SQL 2012新增的Throw ***************/ /********* 3w@live.cn 邀月***************/ SET XACT_ABORT OFF BEGIN TRY BEGIN TRAN INSERT INTO score VALUES (101,80) INSERT INTO score VALUES (102,87) INSERT INTO score VALUES (107, 59) /* 外键错误 */ INSERT INTO score VALUES (103,100) INSERT INTO score VALUES (104,99) COMMIT TRAN PRINT '事务提交' END TRY BEGIN CATCH ROLLBACK; PRINT '事务回滚'; Throw; END CATCH GO　　不过，说实话，Throw好像很简练。

　　说到这里，我有一个疑问：例四和例五的查询结果相同：

/* (1 row(s) affected) (1 row(s) affected) (0 row(s) affected) 事务回滚 Msg 547, Level 16, State 0, Line 13 The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Score__stuid__18B6AB08". The conflict occurred in database "testDb2", table "dbo.Student", column 'stuid'. */　　虽然因为回滚而没有插入数据，但是两个“(1 row(s) affected) ”还是让我吃了一惊， 哪位高手能告诉我一下，这影响的两行SQL Server究竟是怎么处理的？先谢过了。

　　既然，错误已经被捕获，那么有两种处理方式，一是直接在数据库中记录到表中。比如：我们可以建立一个数据库DBErrorLogs,

/*********　生成错误日志记录表　******/ /********* 3w@live.cn 邀月***************/ CREATE database DBErrorLogs GO USE DBErrorLogs GO CREATE TABLE [dbo].[ErrorLog]( [nId] [bigint] IDENTITY(101,1) NOT NULL PRIMARY KEY, [dtDate] [datetime] NOT NULL, [sThread] [varchar](100) NOT NULL, [sLevel] [varchar](200) NOT NULL, [sLogger] [varchar](500) NOT NULL, [sMessage] [varchar](3000) NOT NULL, [sException] [varchar](4000) NULL ) GO ALTER TABLE [dbo].[ErrorLog] ADD DEFAULT (getdate()) FOR [dtDate] GO　　在出错时直接插入相应信息到该表中即可。另外一种思路是交给应用程序来处理，比如下例中，我们用C#捕获错误，并用log4net记录回数据库中。C#中有相应的SQLException类，封装了相应的Error的等级、编号、出错信息等，真心方便。using System; using System.Text; using System.Data.SqlClient; using System.Data; namespace RaiseErrorDemo_Csharp { public class Program { #region Define Members private static log4net.ILog myLogger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); static string conn = "Data Source=AP4\\Net2012;Initial Catalog=Testdb2;Integrated Security=True"; static string sql_RaiseError = @" /********* 使用RaiseError 提交一个错误信息***************/ /********* 3w@live.cn 邀月 ***************/ SET XACT_ABORT OFF BEGIN TRY BEGIN TRAN INSERT INTO Score VALUES (101,80) INSERT INTO Score VALUES (102,87) INSERT INTO Score VALUES (107, 59) /* 外键错误 */ INSERT INTO Score VALUES (103,100) INSERT INTO Score VALUES (104,99) COMMIT TRAN PRINT '事务提交' END TRY BEGIN CATCH ROLLBACK PRINT '事务回滚';--构造一个错误信息记录 DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH "; static string sql_Throw = @" SET XACT_ABORT OFF BEGIN TRY BEGIN TRAN INSERT INTO score VALUES (101,80) INSERT INTO score VALUES (102,87) INSERT INTO score VALUES (107, 59) /* 外键错误 */ INSERT INTO score VALUES (103,100) INSERT INTO score VALUES (104,99) COMMIT TRAN PRINT '事务提交' END TRY BEGIN CATCH ROLLBACK; PRINT '事务回滚'; Throw; END CATCH "; #endregion #region Methods /// <summary> /// 主函数 /// </summary> /// <param name="args"></param> static void Main(string[] args) { CatchSQLError(sql_RaiseError); Console.WriteLine("-----------------------------------------------"); CatchSQLError(sql_Throw); Console.ReadKey(); } /// <summary> /// 捕获错误信息 /// </summary> /// <param name="strSQL"></param> public static void CatchSQLError(string strSQL) { string connectionString = conn; SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd2 = new SqlCommand(strSQL, connection); cmd2.CommandType = CommandType.Text; try { connection.Open(); cmd2.ExecuteNonQuery(); } catch (SqlException err) { string strErr = GetPreError(err.Class); //显示出错信息 Console.WriteLine("错误等级:" + err.Class + Environment.NewLine + strErr + err.Message); //记录错误到数据库中 myLogger.Error(strErr, err); } finally { connection.Close(); } } /// <summary> /// 辅助函数 /// </summary> /// <param name="b"></param> /// <returns></returns> public static string GetPreError(byte b) { string strErr = string.Empty; if (b >= 0 && b <= 10) { strErr = "信息性信息:"; } else if (b >= 11 && b <= 16) { strErr = "用户可以纠正的数据库引擎错误:"; } else if (b >= 17 && b <= 19) { strErr = "需要DBA注意的错误:"; } else if (b >= 20 && b <= 25) { strErr = "致命错误或系统问题:"; } else { strErr = "地球要毁灭了，快跑啊:"; } return strErr; } #endregion } }文后附有C#源码。执行效果：

小结：

1、SQL Server处理错误时有一个重要的开关XACT_ABORT，没事的时候，记得把它打开。

2、SQL Server提供的错误信息很丰富，请区分等级采取相应的对策，当然，还可以自己增加更为实用贴切的自定义错误类型。

下载源码

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助! 3w@live.cn