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

分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.csdn.net/jiangjunshow

也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！

               

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 NULLDROP TABLE [Score]GOIF NOT OBJECT_ID('Student') IS NULLDROP TABLE [Student]GOCREATE TABLE Student(stuid int NOT NULL PRIMARY KEY,stuName Nvarchar(20))CREATE TABLE Score(stuid int NOT NULL REFERENCES Student(stuid),--外键scoreValue int)GOINSERT INTO Student VALUES (101,'胡一刀')INSERT INTO Student VALUES (102,'袁承志')INSERT INTO Student VALUES (103,'陈家洛')INSERT INTO student VALUES (104,'张三丰')GOSELECT * FROM Student/*stuid stuName101 胡一刀102 袁承志103 陈家洛104 张三丰*/

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

例一：

/********* 调用运行时错误 ***************//********* 3w@live.cn 邀月***************/SET XACT_ABORT OFFBEGIN TRANINSERT 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 TRANGO 

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

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

/*(1 row(s) affected)(1 row(s) affected)Msg 547, Level 16, State 0, Line 5The 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 scoreValue101 80102 87103 100104 99*/

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

例二：

/********* 调用运行时错误 ***************//********* 3w@live.cn 邀月***************/TRUNCATE table ScoreGOSET XACT_ABORT OFFBEGIN TRANINSERT 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<>0ROLLBACK TRANELSECOMMIT TRANGO 

　　我先提示一下大家，这个语句中的@@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 OFFBEGIN TRYBEGIN TRANINSERT 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 TRANPRINT '事务提交'END TRYBEGIN CATCHROLLBACKPRINT '事务回滚' --构造一个错误信息记录SELECT ERROR_NUMBER() AS 错误号,ERROR_SEVERITY() AS 错误等级,ERROR_STATE() as 错误状态,DB_ID() as 数据库ID,DB_NAME() as 数据库名称,ERROR_MESSAGE() as 错误信息;END CATCHGO

　　这个返回结果比较另类，它其实是一条拼凑起来的记录。

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

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

例四：

/********* 使用RaiseError 提交一个错误信息***************//********* 3w@live.cn 邀月 ***************/SET XACT_ABORT OFFBEGIN TRYBEGIN TRANINSERT 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 TRANPRINT '事务提交'END TRYBEGIN CATCHROLLBACKPRINT '事务回滚';--构造一个错误信息记录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 CATCHGO 

　　或者直接使用Throw也能达到RaiseError同样的效果，而且这是微软推崇的方式：其官方解释为“ THROW 语句支持 SET XACT_ABORT，但 RAISERROR 不支持。 新应用程序应该改用 THROW，而不使用 RAISERROR。”其实，可能是微软在忽悠，因为，其实RaiseError也支持Set XACT_ABORT。

例五：

/********* SQL 2012新增的Throw ***************//********* 3w@live.cn 邀月***************/SET XACT_ABORT OFFBEGIN TRYBEGIN TRANINSERT 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 TRANPRINT '事务提交'END TRYBEGIN CATCHROLLBACK;PRINT '事务回滚';Throw;END CATCHGO

　　不过，说实话，Throw好像很简练。

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

/*(1 row(s) affected)(1 row(s) affected)(0 row(s) affected)事务回滚Msg 547, Level 16, State 0, Line 13The 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 DBErrorLogsGOUSE DBErrorLogsGOCREATE 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)GOALTER 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 Membersprivate 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 OFFBEGIN TRYBEGIN TRANINSERT 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 TRANPRINT '事务提交'END TRYBEGIN CATCHROLLBACKPRINT '事务回滚';--构造一个错误信息记录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 OFFBEGIN TRYBEGIN TRANINSERT 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 TRANPRINT '事务提交'END TRYBEGIN CATCHROLLBACK;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

           

给我老师的人工智能教程打call！http://blog.csdn.net/jiangjunshow