SqlServer事务回滚(2)

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

SQL事务
 
一、事务概念
    事务是一种机制、是一种操作序列，它包含了一组数据库操作命令，这组命令要么全部执行，要么全部不执行。因此事务是一个不可分割的工作逻辑单元。在数据库系统上执行并发操作时事务是作为最小的控制单元来使用的。这特别适用于多用户同时操作的数据通信系统。例如：订票、银行、保险公司以及证券交易系统等。
 
二、事务属性
事务4大属性：
1   原子性(Atomicity):事务是一个完整的操作。
2   一致性（Consistency）：当事务完成时，数据必须处于一致状态。
3   隔离性(Isolation):对数据进行修改的所有并发事务是彼此隔离的。
4   持久性(Durability):事务完成后，它对于系统的影响是永久性的。
 
三、创建事务
T-SQL中管理事务的语句：
1 开始事务: begin transaction
2 提交事务：commit transaction
3 回滚事务: rollback transaction
 
事务分类:
1 显式事务:用begin transaction明确指定事务的开始。
2 隐性事务：打开隐性事务：set implicit_transactions on，当以隐性事务模式操作时，SQL Servler将在提交或回滚事务后自动启动新事务。无法描述事务的开始，只需要提交或回滚事务。
3 自动提交事务：SQL Server的默认模式，它将每条单独的T-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提供的错误信息很丰富，请区分等级采取相应的对策，当然，还可以自己增加更为实用贴切的自定义错误类型。

下载源码