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




评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值