加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > MsSql教程 > 正文

sqlserver事务处理

发布时间：2020-12-12 13:09:51 所属栏目：MsSql教程 来源：网络整理
导读：SqlServer事务回滚(2) SQL Server 2008中SQL应用系列--目录索引 SQL事务 ? 一、事务概念 ??? 事务是一种机制、是一种操作序列，它包含了一组数据库操作命令，这组命令要么全部执行，要么全部不执行。因此事务是一个不可分割的工作逻辑单元。在数据库系统上执

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，处理错误显得更为精简。本文对此作一个小小的展示。

　　首先，我们假定两个基本表如下：

  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. */??



　　我们从一个最简单的例子入手：

例一：

?

  1. /*********?调用运行时错误?***************/??
  2. /*********?3w@live.cn?邀月***************/??
  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条！　

?

  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?database?"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. */??


　　我对这个结果也有点惊讶，我希望它出错回滚，于是修改：

例二：

  1. /*********?调用运行时错误?***************/??
  2. /*********?3w@live.cn?邀月***************/??
  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（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时也能按照我们的意愿回滚。

例三：

  1. /*********?使用Try?Catch?构造一个错误记录?***************/??
  2. /*********?3w@live.cn?邀月?***************/??
  3. SET?XACT_ABORT?OFF??
  4. BEGIN?TRY??
  5. BEGIN?TRAN??
  6. INSERT?INTO?Score?VALUES?(101,?59)?/*?外键错误?*/??
  7. INSERT?INTO?Score?VALUES?(103,99)??
  8. COMMIT?TRAN??
  9. PRINT?'事务提交'??
  10. END?TRY??
  11. BEGIN?CATCH??
  12. ROLLBACK??
  13. PRINT?'事务回滚'?--构造一个错误信息记录??
  14. ??
  15. SELECT?ERROR_NUMBER()?AS?错误号,??
  16. ERROR_SEVERITY()?AS?错误等级,??
  17. ERROR_STATE()?as?错误状态,??
  18. DB_ID()?as?数据库ID,??
  19. DB_NAME()?as?数据库名称,??
  20. ERROR_MESSAGE()?as?错误信息;??
  21. END?CATCH??
  22. GO??

　　这个返回结果比较另类，它其实是一条拼凑起来的记录。

　　记录并没有新增，因为Catch到错误而事务回滚了。

　　使用RaiseError也可以把出错的信息抛给应用程序来处理。

例四：

  1. /*********?使用RaiseError?提交一个错误信息***************/??
  2. /*********?3w@live.cn?邀月?***************/??
  3. SET?XACT_ABORT?OFF??
  4. BEGIN?TRY??
  5. BEGIN?TRAN??
  6. INSERT?INTO?Score?VALUES?(101,99)??
  7. COMMIT?TRAN??
  8. PRINT?'事务提交'??
  9. END?TRY??
  10. BEGIN?CATCH??
  11. ROLLBACK??
  12. PRINT?'事务回滚';--构造一个错误信息记录??
  13. ??
  14. DECLARE?@ErrorMessage?NVARCHAR(4000);??
  15. DECLARE?@ErrorSeverity?INT;??
  16. DECLARE?@ErrorState?INT;??
  17. ??
  18. SELECT?@ErrorMessage?=?ERROR_MESSAGE(),??
  19. @ErrorSeverity?=?ERROR_SEVERITY(),??
  20. @ErrorState?=?ERROR_STATE();??
  21. ??
  22. RAISERROR?(@ErrorMessage,?--?Message?text.??
  23. @ErrorSeverity,?--?Severity.??
  24. @ErrorState?--?State.??
  25. );??
  26. END?CATCH??
  27. GO???

　　或者直接使用Throw也能达到RaiseError同样的效果，而且这是微软推崇的方式：其官方解释为“THROW 语句支持 SET XACT_ABORT，但 RAISERROR 不支持。 新应用程序应该改用 THROW，而不使用 RAISERROR。”其实，可能是微软在忽悠，因为，其实RaiseError也支持Set XACT_ABORT。

例五：

?

  1. /*********?SQL?2012新增的Throw?***************/??
  2. /*********?3w@live.cn?邀月***************/??
  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好像很简练。

　　说到这里，我有一个疑问：例四和例五的查询结果相同：

  1. /*??
  2. (1?row(s)?affected)??
  3. (1?row(s)?affected)??
  4. (0?row(s)?affected)??
  5. 事务回滚??
  6. Msg?547,?Line?13??
  7. The?INSERT?statement?conflicted?with?the?FOREIGN?KEY?constraint?"FK__Score__stuid__18B6AB08".?The?conflict?occurred?in?database?"testDb2",?column?'stuid'.??
  8. */??

　　虽然因为回滚而没有插入数据，但是两个“(1 row(s) affected) ”还是让我吃了一惊，哪位高手能告诉我一下，这影响的两行SQL Server究竟是怎么处理的？先谢过了。

　　既然，错误已经被捕获，那么有两种处理方式，一是直接在数据库中记录到表中。比如：我们可以建立一个数据库DBErrorLogs,

  1. /*********　生成错误日志记录表　******/??
  2. ??
  3. /*********?3w@live.cn?邀月***************/??
  4. CREATE?database?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的等级、编号、出错信息等，真心方便。

  1. using?System;??
  2. using?System.Text;??
  3. using?System.Data.SqlClient;??
  4. using?System.Data;??
  5. ??
  6. namespace?RaiseErrorDemo_Csharp??
  7. {??
  8. public?class?Program??
  9. {??
  10. #region?Define?Members??
  11. private?static?log4net.ILog?myLogger?=?log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);??
  12. static?string?conn?=?"Data?Source=AP4Net2012;Initial?Catalog=Testdb2;Integrated?Security=True";??
  13. static?string?sql_RaiseError?=?@"??
  14. /*********?使用RaiseError?提交一个错误信息***************/??
  15. /*********?3w@live.cn?邀月?***************/??
  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(),?--?Message?text.??
  36. @ErrorSeverity,?--?Severity.??
  37. @ErrorState?--?State.??
  38. );??
  39. END?CATCH??
  40. ";??
  41. static?string?sql_Throw?=?@"??
  42. SET?XACT_ABORT?OFF??
  43. BEGIN?TRY??
  44. BEGIN?TRAN??
  45. INSERT?INTO?score?VALUES?(101,80)??
  46. INSERT?INTO?score?VALUES?(102,87)??
  47. INSERT?INTO?score?VALUES?(107,?59)?/*?外键错误?*/??
  48. INSERT?INTO?score?VALUES?(103,100)??
  49. INSERT?INTO?score?VALUES?(104,99)??
  50. COMMIT?TRAN??
  51. PRINT?'事务提交'??
  52. END?TRY??
  53. BEGIN?CATCH??
  54. ROLLBACK;??
  55. PRINT?'事务回滚';??
  56. Throw;??
  57. END?CATCH??
  58. ";??
  59. #endregion??
  60. ?
  61. #region?Methods??
  62. ??
  63. ///?<summary>??
  64. ///?主函数??
  65. ///?</summary>??
  66. ///?<param?name="args"></param>??
  67. static?void?Main(string[]?args)??
  68. {??
  69. CatchSQLError(sql_RaiseError);??
  70. Console.WriteLine("-----------------------------------------------");??
  71. CatchSQLError(sql_Throw);??
  72. Console.ReadKey();??
  73. }??
  74. ??
  75. ///?<summary>??
  76. ///?捕获错误信息??
  77. ///?</summary>??
  78. ///?<param?name="strSQL"></param>??
  79. ??
  80. public?static?void?CatchSQLError(string?strSQL)??
  81. {??
  82. string?connectionString?=?conn;??
  83. SqlConnection?connection?=?new?SqlConnection(connectionString);??
  84. SqlCommand?cmd2?=?new?SqlCommand(strSQL,?connection);??
  85. cmd2.CommandType?=?CommandType.Text;??
  86. try??
  87. {??
  88. connection.Open();??
  89. cmd2.ExecuteNonQuery();??
  90. }??
  91. catch?(SqlException?err)??
  92. {??
  93. string?strErr?=?GetPreError(err.Class);??
  94. //显示出错信息??
  95. Console.WriteLine("错误等级:"?+?err.Class?+?Environment.NewLine?+?strErr?+?err.Message);??
  96. //记录错误到数据库中??
  97. myLogger.Error(strErr,?err);??
  98. }??
  99. finally??
  100. {??
  101. connection.Close();??
  102. }??
  103. }??
  104. ///?<summary>??
  105. ///?辅助函数??
  106. ///?</summary>??
  107. ///?<param?name="b"></param>??
  108. ///?<returns></returns>??
  109. public?static?string?GetPreError(byte?b)??
  110. {??
  111. string?strErr?=?string.Empty;??
  112. if?(b?>=?0?&&?b?<=?10)??
  113. {??
  114. strErr?=?"信息性信息:";??
  115. }??
  116. else?if?(b?>=?11?&&?b?<=?16)??
  117. {??
  118. strErr?=?"用户可以纠正的数据库引擎错误:";??
  119. }??
  120. else?if?(b?>=?17?&&?b?<=?19)??
  121. {??
  122. strErr?=?"需要DBA注意的错误:";??
  123. }??
  124. else?if?(b?>=?20?&&?b?<=?25)??
  125. {??
  126. strErr?=?"致命错误或系统问题:";??
  127. }??
  128. else??
  129. {??
  130. strErr?=?"地球要毁灭了，快跑啊:";??
  131. }??
  132. return?strErr;??
  133. }??
  134. ?
  135. #endregion??
  136. ??
  137. }??
  138. }??

文后附有C#源码。执行效果：

?

小结：

1、SQL Server处理错误时有一个重要的开关XACT_ABORT，没事的时候，记得把它打开。

2、SQL Server提供的错误信息很丰富，请区分等级采取相应的对策，当然，还可以自己增加更为实用贴切的自定义错误类型。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同