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

2021年9月16日 1点热度 0条评论 来源: downmoon

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

    原文作者:downmoon
    原文地址: https://blog.csdn.net/downmoon/article/details/7436683
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。