经常用到的SQL Server事务处理

发布时间:2020-09-26编辑:脚本学堂
经常用到的SQL Server事务处理,记录一下,有需要的朋友也可以参考下。

经常用到的SQL Server事务处理,记录一下,有需要的朋友也可以参考下。

一、事务说明

事务是对数据库执行的一个操作单位
Commit Tran: 用于提交事务,将所做的修改保存到数据库。
Rollback Tran: 用于回滚事务,撤销还没有保存到数据库的命令。
SAVE TRAN : 退回到指定的事务保存点.

二、创建相关表的脚本

复制代码 代码如下:

--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
    )

三、几种写法的测试事务脚本

复制代码 代码如下:

----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 TRAN

COMMIT 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 TRAN

COMMIT TRAN

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

BEGIN TRAN tempTran
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.退到指定的事务保存点
/*
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