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 張三豐
*/

　　我們從一個最簡單的例子入手：

例一：

/********* 調用運作時錯誤 ***************/
/********* [email protected] 邀月***************/
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
*/

SQL Server 2005/2008/2012中事務復原的一個充分條件

　　我對這個結果也有點驚訝，我希望它出錯復原，于是修改：

例二：

/********* 調用運作時錯誤 ***************/
/********* [email protected] 邀月***************/

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條！

SQL Server 2005/2008/2012中事務復原的一個充分條件

　　可能有人開始搖頭了，那麼問題的關鍵在哪兒呢?對，就是這個“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 構造一個錯誤記錄 ***************/
/********* [email protected] 邀月 ***************/
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

　　這個傳回結果比較另類，它其實是一條拼湊起來的記錄。

SQL Server 2005/2008/2012中事務復原的一個充分條件

　　記錄并沒有新增，因為Catch到錯誤而事務復原了。

　　使用RaiseError也可以把出錯的資訊抛給應用程式來處理。

例四：

/********* 使用RaiseError 送出一個錯誤資訊***************/
/********* [email protected] 邀月 ***************/
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 ***************/
/********* [email protected] 邀月***************/
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,

/*********　生成錯誤日志記錄表　******/

/********* [email protected] 邀月***************/
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 送出一個錯誤資訊***************/
/********* [email protected] 邀月 ***************/
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#源碼。執行效果：

SQL Server 2005/2008/2012中事務復原的一個充分條件

小結：

1、SQL Server處理錯誤時有一個重要的開關XACT_ABORT，沒事的時候，記得把它打開。

2、SQL Server提供的錯誤資訊很豐富，請區分等級采取相應的對策，當然，還可以自己增加更為實用貼切的自定義錯誤類型。

下載下傳源碼

邀月注：本文版權由邀月和CSDN共同所有，轉載請注明出處。

助人等于自助! [email protected]

SQL Server 2005/2008/2012中事務復原的一個充分條件

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

Git學習筆記5 merge沖突時二選一

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

GridView終極用法(一)

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述