SQL Server的事务和错误处理

最新推荐文章于 2023-01-09 11:08:54 发布
最新推荐文章于 2023-01-09 11:08:54 发布
阅读量1.2k 收藏
点赞数
分类专栏： 专业技术 文章标签： sql server nested transactions delete insert 数据库

事务：

    事务是将一组任务封闭在一个执行单元中。当在这个执行单元中的所有任务都得以成功执行时，每个事务都以一个特定的任务开始和结束。如果这个执行单元中的任意一个任务执行失败时，这个事务就执行失败。因此，事务的执行结果只有两种：要么成功，要么失败。不完整的执行步骤也将倒致事务的执行失败。 

    用户可以使用下面的语句将两个或更多的T-SQL语句封装到一个事务中：

  • Begin Transaction        (可简写为:Begin Tran)
  • Rollback Transaction    (可简写为:Rollback Tran)
  • Commit Transaction     (可简写为:Commit Tran)

     如果事务中的任何一个任务发生了任意一种错误，那么事务中所有的更改都应当中止。在SQL Server术语中把撤销更改操作的过程称作“回滚”(rollback)。如果一个事务中的所有语句都执行正常，那么在事务中完成的所有更改操作都将一起记录到数据库中。在SQL Server术语中，我们称为：这些更改操作被提交到数据库中(在数据库中得以永久保存) 

下面就是一个事务的例子：

-----------------------------------------------------

USE pubs

DECLARE @intErrorCode INT

BEGIN TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END 

----------------------------------------------------- 

    在真正的处理流程开始之前，BEGIN TRAN语句告知SQL Server把下面的所有操作视为一个事务。这里BEGIN TRAN语句后跟有两个UPDATE语句。如果更新操作中未发生任何错误，那么当SQL Server执行COMMIT TRAN语句后，所有的更改操作都将提交给相应的数据库，并且最终结束这个存储存储过程。如果更新操作中发生了某个错误(这个错误是通过if语句检测到的)，那么执行流程将转向PROBLEM标记的T-SQL语句块。这个语句块的作用是：给用户提示一个错误信息，然后SQL Server回滚在处理流程中发生的所有更改操作。

    注意：确保有COMMIT或ROLLBACK与BEGIN TRAN进行配对出现。 

嵌套事务：

SQL Server支持嵌套事务。这个特性主要意味着：一个新的事务在其前一个事务尚未完成之前就可开始。Transact-SQL允许你通过发出嵌套BEGIN TARN命令进行事务的嵌套操作。我们可以通过检索@@TRANCOUNT自动变量从而获知嵌套的层数：0表示没有嵌套事务，1表示嵌套了一层事务。依此类推。

------现在时间：22:53 2011-4-5。先去吃个饭，下文待续

 

------现在时间：09:33 2011-4-8。有事担搁了，下面继续完成工作

    除了最外层的COMMIT语句，与其他嵌套事务配套的COMMIT语句的执行都不会真正提交任何更改操作到磁盘中，这些COMMIT语句的执行中只是使@@TRANCOUNT自动变量的值减1。然而，ROLLBACK语句不管它是与第几层嵌套事务匹配的，只要它被执行则不论当前有多少层嵌套事务，所有的事务都将被回滚。虽然这些个东东理解起来不是那么简单明了，但是这个机制的存在是有它的现实意义的：如果一个嵌套的COMMIT语句的执行将其匹配的嵌套事务中所做的更改操作永久性的写入了磁盘中（只要写入磁盘就变成永久保存了，这是相对于磁盘是一个永久性存储设备而言的），那么，当一个外层的ROLLBACK语句被执行时，这些更改操作就不能回滚了，因为这些更改操作已经被永久性的写入磁盘了。

    当你显示的开始一个事务后，“自动变量”@@TRANCOUNT的值就会从0增加到1；当你执行COMMIT语句后，其值会减1；当你执行ROLLBACK语句后，其值就会被重置为0。到此为止，你已经看出来了：COMMIT和ROLLBACK这两个语句的执行是不对称的：当你嵌套一个事务时，如图1所示、COMMIT语句的执行总是将当前事务嵌套的层数减1。然后，ROLLBACK语句的执行，如图2所示、将会回滚所有的事务。COMMIT和ROLLBACK的不对称性恰恰正是处理嵌套事务出错的关键所在。

 

图1：一个COMMIT语句的执行总是使当前事务数减1以平衡BEGIN TRANSACTION语句

图1：一个COMMIT语句的执行总是使当前事务数减1以平衡BEGIN TRANSACTION语句

图2：一个ROLLBACK语句总是回滚全部的事务

图2：一个ROLLBACK语句总是回滚全部的事务 

 

当如图1和图2所示，你能嵌套事务并且使用@@TRANCOUNT的值检测当前事务嵌套的层数。到目前为此你也了解了COMMIT和ROLLBACK是具有不对称性的，这种不对称性表现在：COMMIT语句的执行只是使@@TRANCOUNT的值减1，而ROLLBACK语句的执行使@@TRANCOUNT的值被置为0。这也就意味着一个事务的真正提交只发生在最后一个COMMIT语句执行之后，不管你嵌套了多少层事务，只有最后的COMMIT语句的执行会产生真正的作用。

下面是一个嵌套事务的例子： 

-----------------------------------------------------

USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0
BEGIN TRAN
    SELECT 'After BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 1
    DELETE sales
    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
                   -- The value of @@TRANCOUNT is 2
        DELETE titleauthor
    COMMIT TRAN nested
                   -- Does nothing except decrement the value of @@TRANCOUNT

    SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
                   -- The value of @@TRANCOUNT is 1
ROLLBACK TRAN

SELECT 'After ROLLBACK TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0
-- because ROLLBACK TRAN always rolls back all transactions and sets
-- @@TRANCOUNT to 0.

SELECT TOP 5 au_id FROM titleauthor

----------------------------------------------------- 

在上面这个例子中我们可以看到即使执行了嵌套的COMMIT TRAN语句，但是外层的ROLLBACK语句的执行依然会将DELETE titleauthor命令所产生的更改操作撤消。

下面是另一个与上例相类似的嵌套事务示例：  

 

-----------------------------------------------------

USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0
BEGIN TRAN
    SELECT 'After BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 1
    DELETE sales
    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
               -- The value of @@TRANCOUNT is 2
        DELETE titleauthor
    ROLLBACK TRAN
 
    SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
    -- The value of @@TRANCOUNT is 0 because
    -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT
    -- to 0.

IF (@@TRANCOUNT > 0) BEGIN
    COMMIT TRAN -- Never makes it here cause of the ROLLBACK
    SELECT 'After COMMIT TRAN', @@TRANCOUNT
END

SELECT TOP 5 au_id FROM titleauthor 

-----------------------------------------------------

保存点提供一种机制可以只回滚事务的一部分操作。用户可以在一个事务中设置保存点或标记点。保存点定义了一个在事务的部分操作在某个条件下被取消时，事务可以回滚到的位置。SQL Server允许你通过SAVE TRAN语句设定保存点，使用SAVE TRAN不会影响@@TRANCOUNT的值。同样的，回滚保存点（而不是回滚事务）的操作也不会影响@@TRANCOUNT的值。然而，这个回滚操作必须显示的指定这个保存点：使用不带指定保存点名称的ROLLBACK TRAN语句总是回滚整个事务。

在本例中，执行流程永远都不会到达外层的COMMIT TRAN。因为ROLLBACK TRAN回滚了当前进程中的所有事务，并且置@@TRANCOUNT的值为0。除非ROOLBACK TRAN语句在被调用时带有一个“保存点”（save point），否则ROLLBACK TRAN不管是在哪里被调用，它总是回滚所有的事务操作并置@@TRANCOUNT为0。

保存事务和保存点:

 

下面的脚本演示了保存点的使用方法： 

-----------------------------------------------------

USE pubs
SELECT 'Before BEGIN TRAN main', @@TRANCOUNT
   -- The value of @@TRANCOUNT is 0

BEGIN TRAN main
    SELECT 'After BEGIN TRAN main', @@TRANCOUNT
   -- The value of @@TRANCOUNT is 1
    DELETE sales
    SAVE TRAN sales  -- Mark a save point
    SELECT 'After SAVE TRAN sales', @@TRANCOUNT
   -- The value of @@TRANCOUNT is still 1

    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
        -- The value of @@TRANCOUNT is 2
        DELETE titleauthor
        SAVE TRAN titleauthor  -- Mark a save point
        SELECT 'After SAVE TRAN titleauthor', @@TRANCOUNT
        -- The value of @@TRANCOUNT is still 2
    ROLLBACK TRAN sales

    SELECT 'After ROLLBACK TRAN sales', @@TRANCOUNT
     -- The value of @@TRANCOUNT is still 2

    SELECT TOP 5 au_id FROM titleauthor

IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN
    SELECT 'AFTER ROLLBACK TRAN', @@TRANCOUNT
    -- The value of @@TRANCOUNT is 0 because
    -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT
    -- to 0.
END
   
SELECT TOP 5 au_id FROM titleauthor 

-----------------------------------------------------

错误处理：

 

这里的示例针对的是存储过程，存储过程是和数据库进行交互的理想解决方案。当存储过程中发生某个错误时，最好的处理方法是：中止代码的顺序执行而转向执行流程中的另一个代码块或返回至调用应用程序。自动变量@@ERROR在错误处理代码中被用来检测当前发生错误的个数。自动变量@@ERROR包含了错误标识号，这个错误标识号是在用户连接期间的最后一条SQL语句执行后所产生的。当某个语句执行成功后，@@ERROR包括0。我们可以通过在待检测语句执行后立即使用IF语句检测@@ERROR的值来确定这个语句是否执行成功。注意：在待检测语句执行后，请立即使用IF语句检测@@ERROR的值。因为当下一语句执行成功后@@ERROR的值会被置0。当一个可截获的错误发生时，@@ERROR的值就会大于0。但是你必须立即捕获@@ERROR的值，因为SQL Server会在任何命令执行成功后将@@ERROR的值置0。大多数情况下，人们都会在任何INSERT,UPDATE或DELETE语句执行后立即检测@@ERROR的值的改变。 

-----------------------------------------------------

CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11), @title VARCHAR(20), @title_type CHAR(12))
AS

BEGIN TRAN
    INSERT titles(title_id, title, type)
    VALUES (@title_id, @title, @title_type)

    IF (@@ERROR <> 0) BEGIN
        PRINT 'Unexpected error occurred!'
        ROLLBACK TRAN
        RETURN 1
    END

    INSERT titleauthor(au_id, title_id)
    VALUES (@au_id, @title_id)

    IF (@@ERROR <> 0) BEGIN
        PRINT 'Unexpected error occurred!'
        ROLLBACK TRAN
        RETURN 1
    END

COMMIT TRAN

RETURN 0 

-----------------------------------------------------

上述这种错误处理解决方案存在大量的冗余代码，尤其当你的业务逻辑需要多于两个Transant-SQL语句时这种冗余现象来体现出来。一个更好的解决方案是：把错误处理代码封装到一个通用的处理过程中： 

-----------------------------------------------------

CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
                          @title VARCHAR(20), @title_type CHAR(12))
AS

BEGIN TRAN
    INSERT titles(title_id, title, type)
    VALUES (@title_id, @title, @title_type)

    IF (@@ERROR <> 0) GOTO ERR_HANDLER

    INSERT titleauthor(au_id, title_id)
    VALUES (@au_id, @title_id)

    IF (@@ERROR <> 0) GOTO ERR_HANDLER

COMMIT TRAN

RETURN 0

ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1 

-----------------------------------------------------

 

[英语版原文：http://www.codeproject.com/KB/database/sqlservertransactions.aspx?fid=16077&df=90&mpp=25&noise=3&sort=Position&view=Quick]

最爱小蜜蜂
关注
专栏目录
SQL SERVER中 存储过程使用事务与捕获异常
whaxrl的专栏
05-11 6832
一、格式类似于 CREATE PROCEDURE YourProcedure AS BEGIN SET NOCOUNT ON; BEGIN TRY---------------------开始捕捉异常 BEIN TRAN------------------开始事务 UPDATE A SET A.names = B.names F
SQL Server事务及其错误处理
Spark Lee Welcomes Your Coming!
04-05 1143
简介：本文示例中使用pubs数据库进行演示(pubs数据库是在安装SQL Server时作为示例数据库而附加的)。如果你想重构pubs数据库，可以按照下面的步骤安装pubs数据库的最新副本。a. 启动osql命令提示符实用工具，使用系统存储过程sp_detach_db将pubs数据库从SQL Server中进行隔离。    -- 在windows命令提示符下执行如下命令即可完成上述操作：osql -U sa -P "" -Q "exec sp_detach_db 'Pubs'" b. 删除pubs数据库的数
SQL Server 事务、异常和游标
weixin_34121304的博客
07-19 178
建议先阅读存储过程：SQL Server 存储过程 Ø 事务 在数据库中有时候需要把多个步骤的指令当作一个整体来运行，这个整体要么全部成功，要么全部失败，这就需要用到事务。     1、 事务的特点         事务有若干条T-SQL指令组成，并且所有的指令昨晚一个整体提交给数据库系统，执行时，这组指令要么全部执行完成，要么全部取消。因此，事务是一个不可分割的逻辑单元。        ...
SQL SERVER回滚恢复误操作的数据
热门推荐
隋旭峰的专栏
08-04 4万+
在生产数据库做CURD操作时，可能会有执行某条语句误操作的情况发生，针对这个种情况有两点建议： 1、 在SQL SERVER上开启事务确认功能，当执行完语句后确认无误，再提交事务。（开启方法见附件图片）。 2、 新建存储过程，粘贴附件脚本。此存储过程执行后能够自动产生两个操作日志表，自动记录CRUD的所有操作。适用于提交事务后才发现错误的情况。只需要打开表UPDATE_LOG，粘贴Roll
mssqlserver - 查询执行的错误信息以及事务回滚
何涛(点滴汇聚终成大海)
12-23 2477
mssqlserver - 查询执行的错误信息以及事务回滚
SQL Server事务和错误处理
04-07
总之，理解和掌握SQL Server的事务处理和错误处理机制是数据库开发和管理的关键技能。这涉及到对ACID属性的理解、事务隔离级别的选择、异常处理的策略，以及如何在不同开发环境中实现这些概念。熟练运用这些知识，...
SQL SERVER 里的错误处理（try catch)
09-11
在SQL Server中，错误处理是数据库操作中必...总之，`TRY...CATCH`是SQL Server中强大的错误处理工具，它使得数据库开发者能够编写更健壮的SQL代码，确保在出现错误时能够优雅地处理，从而提高系统的稳定性和用户体验。
c#实现sqlserver事务处理示例
09-04
在C#中实现SQL Server事务处理是数据库操作中确保数据一致性的重要手段。事务是一组数据库操作，这些操作要么全部成功，要么全部失败，这保证了数据的完整性和一致性。以下是一个详细的C#实现SQL Server事务处理的...
SQLServer存储过程中事务的使用方法
09-10
在SQL Server中，事务是确保数据一致性的重要机制，特别是在存储过程中。存储过程是一组预编译的SQL语句，可以在数据库中被多次调用，提高了效率并降低了网络流量。本篇将详细介绍如何在SQL Server存储过程中使用...
SQL Server触发器和事务用法示例
09-10
在SQL Server中，触发器和事务是数据库管理中不可或缺的部分，它们主要用于确保数据的完整性和一致性。触发器是一种特殊的存储过程，它会在特定的数据库操作（如INSERT、UPDATE或DELETE）发生时自动执行，而事务则...
MySQL事务提交过程中报错问题 （隔离级别隐式提交）
最新发布
weixin_44301987的博客
01-09 849
MySQL事务提交过程中的一些问题 自动提交 事务嵌套。 执行第一条SQL成功，第二条SQL失败，commit后为什么会有数据
SQL Server作业没有执行的解决方法
runwuwushengxiyu的博客
06-29 788
SQL Server作业没有执行的解决方法
C#数据库事务原理及实践
09-23 370
什么是数据库事务　　数据库事务是指作为单个逻辑工作单元执行的一系列操作。设想网上购物的一次交易，其付款过程至少包括以下几步数据库操作：　　· 更新客户所购商品的库存信息 　　· 保存客户付款信息--可能包括与银行系统的交互 　　· 生成订单并且保存到数据库中 　　· 更新用户相关信息，例如购物数量等等 正常的情况下，这些操作将顺利进行，最终交易成功，与交易相关的所有数据库信息也成...
SQL Server-- 存储过程中错误处理
FANET
03-17 1万+
一、存储过程中使用事务的简单语法在存储过程中使用事务时非常重要的，使用数据可以保持数据的关联完整性，在Sql server存储过程中使用事务也很简单，用一个例子来说明它的语法格式：Create Procedure  MyProcedure    (   @Param1       nvarchar(10),       @param2    nvarchar(10)    )    AS       BeginSet NOCOUNT ON;           Set XACT_ABORT ON;     
SQLServer之创建事务未提交读
weixin_33695450的博客
12-03 291
未提交读注意事项 使用 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 指定会话的锁定级别。 一次只能设置一个隔离级别选项，而且设置的选项将一直对那个连接始终有效，直到显式更改该选项为止。 事务中执行的所有读取操作都会在指定的隔离级别的规则下运行，除非语句的 FROM 子句中的表提示为表指定了其他锁定行为或版本控制行为。 事务隔离级别定义了可为读取...
SQL事务回滚的问题及其解决的方法
peilamei1223的博客
09-25 5835
Begin Transaction：开始一个事务；  Commit Transaction：提交事务；  Rollback Transaction：回滚事务。  其中Commit Transaction 与Rollback Transaction 都必须有对应的Begin Transaction 才能正确被执行。  如：  Begin Tran  ……  Rollback
事务执行sql脚本，错误后回滚
m0_53077601的博客
05-13 3938
1.手动commit，rollback -- 开启事务 START TRANSACTION; -- 关闭自动提交 SET autocommit = 0; -- 要执行的sql INSERT INTO `t_bank_organ_copy1` VALUES ( 1, 975, '06285', '中国银行苏州分行', 1, 'root', '2022-05-10 17:55:46', 'root', '2022-05-10 17:55:46' ); INSERT INTO `t_bank_organ_
SQLSERVER SQL执行大脚本文件时，提示“内存不足”的解决办法
qq_17855361的博客
11-20 3880
关于“内存不足”的问题！
mysql 事务 执行错误_Mysql事务操作失败如何解决
weixin_35149412的博客
01-19 1319
Mysql事务操作失败如何解决事务的原子性 ：事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做 。要实现事务的原子性，单单靠一条commit或是rollback命令还是不行的，因为例如commit命令它只是将一个事务中执行成功的DML语句提交给数据库里。如果要实现事务的原子性，则就需要commit和rollback命令配合上程序上的一个业务逻辑才能可以，具体业务逻辑代码如下示例代...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值