SqlServer事务回滚（2）
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 隐性事务：打开隐性事务：t implicit_transactions on，当以隐性事务模式操作时，SQL Servler将在提交或回滚事务后⾃动启动新事务。⽆法描述事务的开始，只需要提交或回滚事务。
3 ⾃动提交事务：SQL Server的默认模式，它将每条单独的T-SQL语句视为⼀个事务。如果成功执⾏，则⾃动提交，否则回滚。
在SQL Server 2000中，我们⼀般使⽤RaiError（）来抛出错误交给应⽤程序来处理。看MSDN⽰例（），⾃从SQL Server 2005集成Try…Catch功能以后，我们使⽤时更加灵活，到了SQL Server 2012，更推出了强⼤的THROW，处理错误显得更为精简。本⽂对此作⼀个⼩⼩的展⽰。
⾸先，我们假定两个基本表如下：
[sql]
1. --创建两个测试表
2.
3. IF NOT OBJECT_ID('Score') IS NULL
4. DROP TABLE [Score]
5. GO
6. IF NOT OBJECT_ID('Student') IS NULL
7. DROP TABLE [Student]
8. GO
9.
10. CREATE TABLE Student
11. (stuid int NOT NULL PRIMARY KEY,
12. stuName Nvarchar(20)
13. )
14. CREATE TABLE Score
15. (stuid int NOT NULL REFERENCES Student(stuid),--外键
16. scoreValue int
17. )
18. GO
19.
20. INSERT INTO Student VALUES (101,'胡⼀⼑')
21. INSERT INTO Student VALUES (102,'袁承志')
22. INSERT INTO Student VALUES (103,'陈家洛')
23. INSERT INTO student VALUES (104,'张三丰')
24. GO
25.
数码产品26. SELECT * FROM Student
27.
28. /*
29. stuid stuName
30. 101 胡⼀⼑
31. 102 袁承志
32. 103 陈家洛
33. 104 张三丰
34. */
我们从⼀个最简单的例⼦⼊⼿：
例⼀：
[sql]
1. /********* 调⽤运⾏时错误 ***************/
2. /********* 邀⽉***************/
3. SET XACT_ABORT OFF
4. BEGIN TRAN
5. INSERT INTO Score VALUES (101,80)
6. INSERT INTO Score VALUES (102,87)
7. INSERT INTO Score VALUES (107, 59) /* 外键错误 */
8. -----SELECT 1/0 /* 除数为0错误 */
9. INSERT INTO Score VALUES (103,100)
10. INSERT INTO Score VALUES (104,99)
11. COMMIT TRAN
12. GO
先不看结果，我想问⼀下，该语句执⾏完毕后，Score表会插⼊⼏条记录？估计可能有⼈说是2条，有⼈说0条，也可能有⼈说4条。
实际上，我希望是0条，但结果是4条！
[sql]
1. /*
2. (1 row(s) affected)
3. (1 row(s) affected)
4. Msg 547, Level 16, State 0, Line 5
5. The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Score__stuid__01D345B0". The conflict occurred in databa "testDb2", table "dbo.Student", column 'stuid'.
6. The statement has been terminated.
7. (1 row(s) affected)
8. (1 row(s) affected)
9. */
10.
11. SELECT * from Score
12. /*
13. stuid scoreValue
14. 101 80
15. 102 87
16. 103 100
17. 104 99
18. */
我对这个结果也有点惊讶，我希望它出错回滚，于是修改：
例⼆：
[sql]
1. /********* 调⽤运⾏时错误 ***************/
2. /********* 邀⽉***************/
3.
4. TRUNCATE table Score
5. GO
6.
7.
8. SET XACT_ABORT OFF
9. BEGIN TRAN
10. INSERT INTO Score VALUES (101,80)
11. INSERT INTO Score VALUES (102,87)
12. INSERT INTO Score VALUES (107, 59) /* 外键错误 */
13. ----SELECT 1/0
14. --INSERT INTO Score VALUES (103,100)
15. --INSERT INTO Score VALUES (104,99)
16.
17. PRINT '@@ERROR是:'+cast(@@ERROR as nvarchar(10))
18. IF @@ERROR<>0
19. ROLLBACK TRAN
20. ELSE
21. COMMIT TRAN
22. GO胡子英文
我先提⽰⼀下⼤家，这个语句中的@@ERROR值是547，那么此时，Score表中有⼏条记录?
答案是2条！
可能有⼈开始摇头了，那么问题的关键在哪⼉呢?对，就是这个“XACT_ABORT ”开关，查MSDN（），
官⽅解释：它⽤于指定当 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。唯⼀不需要该选项的情况是在提供程序⽀持嵌套事务时。
这⾥，红⾊的⼀句话是关键，那么“有时”究竟是指什么时候呢？查资料知：（）
⼤致分为以下四个级别：
当等级SEVERITY为0-10时，为“信息性消息”，最轻。
当等级为11-16时，为“⽤户可以纠正的数据库引擎错误”。如除数为零，等级为16
当等级为17-19时，为“需要DBA注意的错误”。如内存不⾜、数据库引擎已到极限等。
当等级为20-25时，为“致命错误或系统问题”。如硬件或软件损坏、完整性问题、媒体故障等。
⽤户也可以⾃定义错误级别和类型。
根据以上解释，我们最保险的⽅式是：Set XACT_ABORT ON。
当然，使⽤Try…Catch在Set XACT_ABORT OFF时也能按照我们的意愿回滚。
例三：
[sql]
1. /********* 使⽤Try Catch 构造⼀个错误记录 ***************/
2. /********* 邀⽉ ***************/
3. SET XACT_ABORT OFF
4. BEGIN TRY
颜体字体5. BEGIN TRAN
6. INSERT INTO Score VALUES (101,80)
7. INSERT INTO Score VALUES (102,87)
8. INSERT INTO Score VALUES (107, 59) /* 外键错误 */
9. INSERT INTO Score VALUES (103,100)
10. INSERT INTO Score VALUES (104,99)
11. COMMIT TRAN
12. PRINT '事务提交'
13. END TRY
14. BEGIN CATCH
15. ROLLBACK
16. PRINT '事务回滚' --构造⼀个错误信息记录
17.
18. SELECT ERROR_NUMBER() AS 错误号,
19. ERROR_SEVERITY() AS 错误等级,
20. ERROR_STATE() as 错误状态,
21. DB_ID() as 数据库ID,
22. DB_NAME() as 数据库名称,
23. ERROR_MESSAGE() as 错误信息;
24. END CATCH
25. GO
这个返回结果⽐较另类，它其实是⼀条拼凑起来的记录。
记录并没有新增，因为Catch到错误⽽事务回滚了。
使⽤RaiError也可以把出错的信息抛给应⽤程序来处理。
例四：
[sql]
1. /********* 使⽤RaiError 提交⼀个错误信息***************/
2. /********* 邀⽉ ***************/
3. SET XACT_ABORT OFF
4. BEGIN TRY
5. BEGIN TRAN
6. INSERT INTO Score VALUES (101,80)
7. INSERT INTO Score VALUES (102,87)
8. INSERT INTO Score VALUES (107, 59) /* 外键错误 */
9. INSERT INTO Score VALUES (103,100)
10. INSERT INTO Score VALUES (104,99)
11. COMMIT TRAN
12. PRINT '事务提交'
13. END TRY
14. BEGIN CATCH
15. ROLLBACK
16. PRINT '事务回滚';--构造⼀个错误信息记录
17.
18. DECLARE @ErrorMessage NVARCHAR(4000);
19. DECLARE @ErrorSeverity INT;
20. DECLARE @ErrorState INT;
21.
22. SELECT @ErrorMessage = ERROR_MESSAGE(),
23. @ErrorSeverity = ERROR_SEVERITY(),
24. @ErrorState = ERROR_STATE();
25.excel美化
26. RAISERROR (@ErrorMessage, -- Message text.
27. @ErrorSeverity, -- Severity.
28. @ErrorState -- State.
29. );
30. END CATCH
31. GO
或者直接使⽤Throw也能达到RaiError同样的效果，⽽且这是微软推崇的⽅式：其官⽅解释为“THROW 语句⽀持 SET XACT_ABORT，但 RAISERROR 不⽀持。新应⽤程序应该改⽤ THROW，⽽不使⽤ RAISERROR。”其实，可能是微软在忽悠，因为，其实RaiError也⽀持Set XACT_ABORT。
例五：
[sql]
1. /********* SQL 2012新增的Throw ***************/
2. /********* 邀⽉***************/
3. SET XACT_ABORT OFF
4. BEGIN TRY
5. BEGIN TRAN
6. INSERT INTO score VALUES (101,80)
7. INSERT INTO score VALUES (102,87)
8. INSERT INTO score VALUES (107, 59) /* 外键错误 */
9. INSERT INTO score VALUES (103,100)
10. INSERT INTO score VALUES (104,99)
11. COMMIT TRAN
12. PRINT '事务提交'
13. END TRY
14. BEGIN CATCH
15. ROLLBACK;
16. PRINT '事务回滚';
17. Throw;
18. END CATCH
19. GO
不过，说实话，Throw好像很简练。
说到这⾥，我有⼀个疑问：例四和例五的查询结果相同：
[sql]
1. /*
2. (1 row(s) affected)
3. (1 row(s) affected)
4. (0 row(s) affected)
5. 事务回滚
6. Msg 547, Level 16, State 0, Line 13
7. The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Score__stuid__18B6AB08". The conflict occurred in databa "testDb2", table "dbo.Student", column 'stuid'.
8. */
虽然因为回滚⽽没有插⼊数据，但是两个“(1 row(s) affected) ”还是让我吃了⼀惊，哪位⾼⼿能告诉我⼀下，这影响的两⾏SQL Server究竟是怎么处理的？先谢过了。
既然，错误已经被捕获，那么有两种处理⽅式，⼀是直接在数据库中记录到表中。⽐如：我们可以建⽴⼀个数据库DBErrorLogs,
[sql]
1. /********* ⽣成错误⽇志记录表 ******/
2.
3. /********* 邀⽉***************/
4. CREATE databa DBErrorLogs
5. GO
6.
7. USE DBErrorLogs
8. GO
9.
10. CREATE TABLE [dbo].[ErrorLog](
11. [nId] [bigint] IDENTITY(101,1) NOT NULL PRIMARY KEY,
12. [dtDate] [datetime] NOT NULL,
13. [sThread] [varchar](100) NOT NULL,
14. [sLevel] [varchar](200) NOT NULL,
15. [sLogger] [varchar](500) NOT NULL,
16. [sMessage] [varchar](3000) NOT NULL,
17. [sException] [varchar](4000) NULL
18. )
19. GO
20.
21. ALTER TABLE [dbo].[ErrorLog] ADD DEFAULT (getdate()) FOR [dtDate]
22. GO
在出错时直接插⼊相应信息到该表中即可。另外⼀种思路是交给应⽤程序来处理，⽐如下例中，我们⽤C#捕获错误，并⽤log4net记录回数据库中。C#中有相应的
SQLException类，封装了相应的Error的等级、编号、出错信息等，真⼼⽅便。
[csharp]
1. using System;
2. using System.Text;
3. using System.Data.SqlClient;
4. using System.Data;
5.
6. namespace RaiErrorDemo_Csharp
扶贫标语
7. {
8. public class Program
9. {
10. #region Define Members
11. private static log4net.ILog myLogger = log4net.LogManager.GetLogger(System.Reflection.MethodBa.GetCurrentMethod().DeclaringType);
监理是什么12. static string conn = "Data Source=AP4\\Net2012;Initial Catalog=Testdb2;Integrated Security=True";
13. static string sql_RaiError = @"
14. /********* 使⽤RaiError 提交⼀个错误信息***************/
15. /********* 邀⽉ ***************/
16. SET XACT_ABORT OFF
17. BEGIN TRY
18. BEGIN TRAN
19. INSERT INTO Score VALUES (101,80)
20. INSERT INTO Score VALUES (102,87)
21. INSERT INTO Score VALUES (107, 59) /* 外键错误 */
22. INSERT INTO Score VALUES (103,100)
23. INSERT INTO Score VALUES (104,99)
24. COMMIT TRAN
25. PRINT '事务提交'
26. END TRY
27. BEGIN CATCH
28. ROLLBACK
29. PRINT '事务回滚';--构造⼀个错误信息记录
30. DECLARE @ErrorMessage NVARCHAR(4000);
31. DECLARE @ErrorSeverity INT;
32. DECLARE @ErrorState INT;
33.
34. SELECT @ErrorMessage = ERROR_MESSAGE(),
35. @ErrorSeverity = ERROR_SEVERITY(),
36. @ErrorState = ERROR_STATE();
37.
38. RAISERROR (@ErrorMessage, -- Message text.
39. @ErrorSeverity, -- Severity.
40. @ErrorState -- State.
41. );
42. END CATCH
43. ";
44. static string sql_Throw = @"
45. SET XACT_ABORT OFF
46. BEGIN TRY
47. BEGIN TRAN
48. INSERT INTO score VALUES (101,80)
49. INSERT INTO score VALUES (102,87)
50. INSERT INTO score VALUES (107, 59) /* 外键错误 */
51. INSERT INTO score VALUES (103,100)
52. INSERT INTO score VALUES (104,99)
53. COMMIT TRAN
54. PRINT '事务提交'
55. END TRY
56. BEGIN CATCH
57. ROLLBACK;
58. PRINT '事务回滚';
59. Throw;
60. END CATCH
61. ";
62. #endregion
63.
64. #region Methods
65.
66. /// <summary>
67. /// 主函数
68. /// </summary>
69. /// <param name="args"></param>
70. static void Main(string[] args)
71. {
72. CatchSQLError(sql_RaiError);
73. Console.WriteLine("-----------------------------------------------");
74. CatchSQLError(sql_Throw);
75. Console.ReadKey();
76. }
77.
78. /// <summary>
79. /// 捕获错误信息
80. /// </summary>
81. /// <param name="strSQL"></param>
82.
83. public static void CatchSQLError(string strSQL)
84. {
85. string connectionString = conn;
明星签名86. SqlConnection connection = new SqlConnection(connectionString);
87. SqlCommand cmd2 = new SqlCommand(strSQL, connection);
88. cmd2.CommandType = CommandType.Text;
89. try
90. {
91. connection.Open();
92. cmd2.ExecuteNonQuery();
93. }
94. catch (SqlException err)
95. {
96. string strErr = GetPreError(err.Class);
97. //显⽰出错信息
98. Console.WriteLine("错误等级:" + err.Class + Environment.NewLine + strErr + err.Message);
99. //记录错误到数据库中
100. myLogger.Error(strErr, err);
101. }
102. finally
103. {
104. connection.Clo();
105. }
106. }
107. /// <summary>
108. /// 辅助函数
109. /// </summary>
110. /// <param name="b"></param>
111. /// <returns></returns>
112. public static string GetPreError(byte b)
113. {
114. string strErr = string.Empty;
115. if (b >= 0 && b <= 10)
116. {
117. strErr = "信息性信息:";
118. }
119. el if (b >= 11 && b <= 16)
120. {
121. strErr = "⽤户可以纠正的数据库引擎错误:";
122. }
123. el if (b >= 17 && b <= 19)
124. {
125. strErr = "需要DBA注意的错误:";
126. }
127. el if (b >= 20 && b <= 25)
128. {
129. strErr = "致命错误或系统问题:";
当你的眼睛眯着笑是什么歌130. }
131. el
132. {
133. strErr = "地球要毁灭了，快跑啊:";
134. }
135. return strErr;
136. }
