SQL Server事务处理

经常用到SQL Server事务，顺便整理了一下

1.事务说明

事务是对数据库执行的一个操作单位

Commit Tran: 用于提交事务，将所做的修改保存到数据库。

Rollback Tran: 用于回滚事务，撤销还没有保存到数据库的命令。

SAVE TRAN : 退回到指定的事务保存点.

2.创建相关表的脚本

--DROP TABLE dbo.tbTest1
--DROP TABLE dbo.tbTest2

CREATE TABLE [dbo].[tbTest1]( 
        [Id] [ int] PRIMARY KEY NOT NULL ,
       [Name] [varchar]( 50) NOT NULL 
)

CREATE TABLE [dbo].[tbTest2]( 
        [Id] [ int] PRIMARY KEY NOT NULL ,
        [Category] [varchar]( 50) NOT NULL
)

3. 几种写法的测试事务脚本如下

----SQL事务处理的写法------------------------------------ 1.测试事务方法
  /* (1)当第一条有效，第2条有误时，会保证2条记录都不写入数据库 */
INSERT INTO dbo.tbTest2(Id, Category )VALUES ( 1, ' 222_old ' )
BEGIN TRAN 
        INSERT INTO dbo.tbTest1(Id, Name )VALUES ( 1, ' 111 ' ) 
        IF @@ERROR<> 0 

              ROLLBACK TRAN 

        INSERT INTO dbo.tbTest2(Id, Category )VALUES ( 1, ' 222 ' ) 
        IF @@ERROR<> 0 

              ROLLBACK TRANCOMMIT TRAN
/* (2)当第一条有误，第2条有效时，第一条不写入数据库，第2条写入数据库 */
INSERT INTO dbo.tbTest1(Id, Name )VALUES ( 2, ' 111_old ' )
BEGIN TRAN 
        INSERT INTO dbo.tbTest1(Id, Name )VALUES ( 2, ' 111 ' )    
        IF @@ERROR<> 0

               ROLLBACK TRAN       
 
        INSERT INTO dbo.tbTest2(Id, Category )VALUES ( 2, ' 222 ' )
        IF @@ERROR<> 0

              ROLLBACK TRANCOMMIT TRAN
 -- 2.保存所有的数据都事务同步(方法1)
  /*  不管哪条数据有问题，都能保证不写入数据库 */
DECLARE @ErrorNum INT

SET @ErrorNum =  0

BEGIN TRAN tempTranI

        INSERT INTO dbo.tbTest1(Id, Name )VALUES ( 3, ' 111 ' )

         set @ErrorNum=@ErrorNum + @@error

 

       INSERT INTO dbo.tbTest2(Id, Category )VALUES ( 2, ' 222 ' )

        set @ErrorNum=@ErrorNum + @@error

 

      IF @ErrorNum >  0

             ROLLBACK TRAN

      ELSE 

             COMMIT TRAN

 

    -- 3.保存所有的数据都事务同步(方法2) 

/*  不管哪条数据有问题，都能保证不写入数据库 */

BEGIN TRY

    BEGIN TRAN tempTran           

         INSERT INTO dbo.tbTest1(Id, Name )VALUES ( 4, ' 111 ' )       

 

         INSERT INTO dbo.tbTest2(Id, Category )VALUES ( 2, ' 222 ' )           

         COMMIT TRAN tempTran    

   END TRY

 BEGIN CATCH    

    RAISERROR 50005N ' have error,please check. '

     ROLLBACK TRAN tempTran

 END CATCH

4. 退回到事务指定的保存点

-- 4.退到指定的事务保存点

/* ISOLATION LEVEL：设置数据隔离级别：REPEATABLE READ：锁定查询中使用的所有数据以防止其他用户更新、删除数据等，但是其他用户可以将新的幻像行插入数据集 */

--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN   

      SAVE TRAN sp1       

 

      INSERT INTO dbo.tbTest1(Id, Name )VALUES ( 6, ' 111 ' )   

 

      SAVE TRAN sp2       

 

      INSERT INTO dbo.tbTest2(Id, Category )VALUES ( 7, ' 222 ' )   

 

      SAVE TRAN sp3

 

       INSERT INTO dbo.tbTest1(Id, Name )VALUES ( 8, ' 111 ' )

 

      SAVE TRAN sp4   

    

     --DECLARE @tempId INT   

     --SET @tempId =  0

     --SELECT @tempId = Id FROM dbo.tbTest2 WHERE Id = 5   

     --IF @tempId <= 0 -- is  true   

     ROLLBACK TRAN sp3

 

COMMIT TRAN