• SQL Server重温——事务


    为什么使用事务
      当对多个表进行更新的时候,某条执行失败。为了保持数据的完整性,需要使用事务回滚。
     
    显示设置事务

      begin try
        begin transaction
        insert into shiwu (asd) values ('aasdasda');
        commit transaction
      end try
      
      begin catch
        select ERROR_NUMBER() as errornumber
        rollback transaction
      end catch

     
    隐式设置事务

    set implicit_transactions on;        -- 启动隐式事务
      go
      
      begin try
        insert into shiwu (asd) values ('aasdasda');
        insert into shiwu (asd) values ('aasdasda');
        commit transaction;
      end try
      
      begin catch
        select ERROR_NUMBER() as errornumber
        rollback transaction;            --回滚事务
      end catch
      
      set implicit_transactions off;    --关闭隐式事务
      go

    显示事务以下语句不能使用,隐式事务可以

      alter database;
      backup;
      create database;
      drop database;
      reconfigure;
      restore;
      update statistics;

    显示事务可以嵌套使用

    --创建存储过程
      create procedure qiantaoProc 
      @asd nchar(10)
      as
      begin
        begin try
            begin transaction innerTrans
            save transaction savepoint        --创建事务保存点
            insert into shiwu (asd) values (@asd);
            commit transaction innerTrans
        end try
        
        begin catch
            rollback transaction savepoint    --回滚到保存点
            commit transaction innerTrans
        end catch
      end
      go
      
      begin transaction outrans
        exec qiantaoProc 'asdasd';
      rollback transaction outrans

    事务嵌套,回滚外层事务时,如果嵌套内的事务已经回滚过则会有异常。此时需要使用事务保存点。如上代码。

  • 相关阅读:
    nowcoderD Xieldy And His Password
    Codeforces681D Gifts by the List
    nowcoder80D applese的生日
    Codeforces961E Tufurama
    Codeforces957 Mahmoud and Ehab and yet another xor task
    nowcoder82E 无向图中的最短距离
    nowcoder82B 区间的连续段
    Codeforces903E Swapping Characters
    Codeforces614C Peter and Snow Blower
    Codeforces614D Skills
  • 原文地址:https://www.cnblogs.com/chuifeng/p/2618246.html
Copyright © 2020-2023  润新知