存储过程中的事务实现

最新推荐文章于 2022-08-22 14:10:06 发布

转载最新推荐文章于 2022-08-22 14:10:06 发布 · 872 阅读

MS SQL Server 同时被 2 个专栏收录

33 篇文章

订阅专栏

数据库资料

27 篇文章

订阅专栏

一直以为存储过程会自动实现事务操作，其实不然。存储过程只是提供的事务操作的支持。要实现事务操作，还得自己实现。

基本上方法有两个：

SET XACT_ABORT

指定当 Transact-SQL 语句产生运行时错误时，Microsoft® SQL Server™ 是否自动回滚当前事务。

语法
SET XACT_ABORT { ON | OFF }

注释
当 SET XACT_ABORT 为 ON 时，如果 Transact-SQL 语句产生运行时错误，整个事务将终止并回滚。为 OFF 时，只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。编译错误（如语法错误）不受 SET XACT_ABORT 的影响。

对于大多数 OLE DB 提供程序（包括 SQL Server），隐性或显式事务中的数据修改语句必须将 XACT_ABORT 设置为 ON。唯一不需要该选项的情况是提供程序支持嵌套事务时。有关更多信息，请参见分布式查询和分布式事务。

SET XACT_ABORT 的设置是在执行或运行时设置，而不是在分析时设置。
例：

create proc testproc
as
SET XACT_ABORT on
begin tran
insert into tableA (field1) values ('aa')
insert into tableB (field1) values ('bb')
commit tran
SET XACT_ABORT off

begin tran

语法

/*要实现的操作*/
       commit tran
      if @@error>0
      rollback

例：

create proc testproc
      as

      begin tran
     insert into tableA (field1) values ('aa')
     insert into tableB (field1) values ('bb')
     commit tran
     if @@error>0
      rollback

另外,在.NET的ADO.NET数据库编程中,可以使用 SqlTransaction实现事务操作。

例：

Public Sub RunSqlTransaction(myConnString As String)
    Dim myConnection As New SqlConnection(myConnString)
    myConnection.Open()
    
    Dim myCommand As SqlCommand = myConnection.CreateCommand()
    Dim myTrans As SqlTransaction
    
    ' Start a local transaction
    myTrans = myConnection.BeginTransaction()
    ' Must assign both transaction object and connection
    ' to Command object for a pending local transaction
    myCommand.Connection = myConnection
    myCommand.Transaction = myTrans
    
    Try
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"
      myCommand.ExecuteNonQuery()
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"
      myCommand.ExecuteNonQuery()
      myTrans.Commit()
      Console.WriteLine("Both records are written to database.")
    Catch e As Exception
      Try
        myTrans.Rollback()
      Catch ex As SqlException
        If Not myTrans.Connection Is Nothing Then
          Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
                            " was encountered while attempting to roll back the transaction.")
        End If
      End Try
    
      Console.WriteLine("An exception of type " & e.GetType().ToString()   "was encountered while inserting the data.")
      Console.WriteLine("Neither record was written to database.")
    Finally
      myConnection.Close()
    End Try
End Sub 'RunSqlTransaction

http://weisai.cnblogs.com/archive/2005/07/07/188000.html

SQL SERVER存储过程中使用事务

Create PROCEDURE UpdateWanjun
@UserName nvarchar(500),
@UserPassword nvarchar(500),
@ReturnVal int output
AS   
    --Set XAcT_ABORT ON
   Begin Transaction T
       Update admins set UserPassword = @UserPassword where UserName = @UserName
       Update admins set UserPassword = @UserPassword,a='aaadfasdfasdfas' where id=4           --出错语句 ,a为int型,大小为默认值4
       --set @ReturnVal=@@Rowcount(这个变量,只能在紧挨着它的上面SQL下起作用,这样才能返回值,只有放在Update下面才管用)
   
   IF @@Error <> 0
    Begin
       Print '111'  
       RollBack Transaction T
     End
   Else
       print '222'
       COMMIT Transaction T
GO

####################################################################################

如果在一个存储过程中执行调用另一个服务器上的存储过程时，添加事务时需要使用

BEGIN DISTRIBUTED TRANSACTION来开始事务（分布式事务）
BEGIN DISTRIBUTED TRAN [ SACTION ]
[ transaction_name | @tran_name_variable ]

参数
transaction_name

是用户定义的事务名，用于跟踪 MS DTC 实用工具中的分布式事务。transaction_name 必须符合标识符规则，但是仅使用头 32 个字符。

@tran_name_variable

是用户定义的一个变量名，它含有一个事务名，该事务名用于跟踪 MS DTC 实用工具中的分布式事务。必须用 char、varchar、nchar 或 nvarchar 数据类型声明该变量。

注释
执行 BEGIN DISTRIBUTED TRANSACTION 语句的服务器是事务创建人，并且控制事务的完成。当连接发出后续 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 语句时，主控服务器请求 MS DTC 在所涉及的服务器间管理分布式事务的完成。

有两个方法可将远程 SQL 服务器登记在一个分布式事务中：

分布式事务中已登记的连接执行一个远程存储过程调用，该调用引用一个远程服务器。

分布式事务中已登记的连接执行一个分布式查询，该查询引用一个远程服务器。
例如，如果在 ServerA 上发出 BEGIN DISTRIBUTED TRANSACTION，该连接调用 ServerB 上的存储过程和 ServerC 上的另一个存储过程，并且 ServerC 上的存储过程对 ServerD 执行一个分布式查询，那么四个 SQL 服务器都进入分布式事务中了。ServerA 是该事务的创建者和控制服务器。

分布式事务 Transact-SQL 涉及的连接并不获取可以传给另一个连接的事务对象，从而也不能用该方法显式登记在分布式事务中。远程服务器登记到事务中的唯一方法是成为远程存储过程调用或分布式查询的目标。

默认情况下，任何有效用户都拥有 BEGIN DISTRIBUTED TRANSACTION 权限。

http://blog.youkuaiyun.com/mfcmfcmfc/article/details/2676725

很多资料中，对于事务的处理都很简单，抓捕SQL错误，仅仅是一个 @@error_count 吗？肯定不是。@@error_count 只记录上一次SQL操作的结果，对于存储过程中多条语句肯定是不行的。

那么，怎么才能保证在存储过程中正确的回滚呢？让我们先来看个例子：

CREATE TABLE `test` (`id` int(11) NOT NULL AUTO_INCREMENT,  
    `content` varchar(100), PRIMARY KEY (`id`) );  
  
DELIMITER $$  
DROP PROCEDURE IF EXISTS  test_sp1 $$  
CREATE PROCEDURE test_sp1( )  
    BEGIN  
        START TRANSACTION;  
            INSERT INTO test VALUES(NULL, 'test sql 001');   /* 第一条 insert 能执行 */  
            INSERT INTO test VALUES('1', 'test sql 002');  /* 第二条 insert，不能执行 */   
  
        IF @@error_count = 0 THEN  
            COMMIT;  
        ELSE  
            ROLLBACK;  
        END IF;    
  
    END$$  
DELIMITER ;

我们在MySQL客户端执行 CALL test_sp1(); 之后会发现，存储过程中第一条insert成功的执行了，而第二条执行没有执行，但是注意！这里发生了错误，而作为判断的 @@error_count 没有反应, ROLLBACK 不能回滚！

在我上一篇文章《执行一半的存储过程 – MySQL邯郸学步》中存储过程执行了一半，而很不幸的，这里的 test_sp1() 也执行了一般，就好比我们PHP、JAVA、C++程序遇到错误一样，抛出error, 然后停止执行。

之前使用PDO，在处理事务时，使用SQLException捕获SQL错误，然后处理；按照这个推论，我们必须在MySQL存储过程中捕获SQL错误，最后判断是回滚(ROLLBACK)还是提交(COMMIT)。所以存储过程可以改为：

DELIMITER $$  
DROP PROCEDURE IF EXISTS  test_sp1 $$  
CREATE PROCEDURE test_sp1( )  
    BEGIN  
    DECLARE t_error INTEGER DEFAULT 0;  
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;  
  
        START TRANSACTION;  
            INSERT INTO test VALUES(NULL, 'test sql 001');   /* 第一条 insert 能执行 */  
            INSERT INTO test VALUES('1', 'test sql 002');  /* 第二条 insert，不能执行 */   
  
        F txn_error = 1 THEN  
            ROLLBACK;  
        ELSE  
            COMMIT;  
        END IF;  
  
    END$$  
DELIMITER ;

第二个例子中，我们为test_sp1() 定义了一个 SQLEXCEPTION 参数 t_error，在遇到SQL错误时，继续执行(CONTINUE); 如果执行状态没有错误，则提交，反之回滚！

如果存储过程A包含B，那么最好只在A中定义事务；记住，保证事务的原子性。

1，保持事务短小
2，尽量避免事务中rollback
3，尽量避免savepoint
4，默认情况下，依赖于悲观锁
5，为吞吐量要求苛刻的事务考虑乐观锁
6，显示声明打开事务
7，锁的行越少越好，锁的时间越短越好