SqlServer——事务一编程进阶(SqlServer技术内幕 T-SQL程序设计 第九章

时间:2023-03-09 06:48:24
SqlServer——事务一编程进阶(SqlServer技术内幕 T-SQL程序设计 第九章
  事务格式如下:
  1、开启事务: begin tran
  2、提交事务:commit tran
  3、回滚事务:rollback tran
  
  判断事务是提交还是应该回滚有两种方式,一是全局变量 @@error,二是 begin try……end try begin catch……end catch
一、@@error
  @@error作为全局变量,保存着每次执行SQL语句的状态值,当值为0时表示执行正常,否则为错误,因此可以定义一个变量,在每次执行一条SQL语句时都要将该变量的值加上@@error的值,到最后判断该变量值若为0则执行正常可以提交事务,否则回滚。
  我们通过银行转账的例子来看一下 @@error 的用法
  1、创建相关表 Bill

create table Bill
(
ID uniqueidentifier primary key,
name varchar(20),
Balance money
)

2、插入数据并查询数据

insert into Bill values (NEWID(),'小龙',4000),(newid(),'小李',2000)

select * from Bill

如图:

SqlServer——事务一编程进阶(SqlServer技术内幕 T-SQL程序设计 第九章

  3、创建存储过程

Create proc Transfer
  @IDFrom uniqueidentifier,@IDTo uniqueidentifier,@Amount money  --参数
as
  declare @errs int =0  --判断出现错误SQL语句的变量,设值为 0
  if (select COUNT(1) from Bill where ID =@IDFrom )>0 and (select COUNT(1) from Bill where ID =@IDTo)>0   --判断转入转出账户是否存在  
      and (select Balance from Bill where ID =@IDFrom)>@Amount+1000  --要求转出账户转出后余额要大于 1000 元
    begin
      begin tran  --开启事务
      update Bill set Balance=Balance -@Amount where ID =@IDFrom
      set @errs =@errs +@@ERROR   --若上一句 update 语句有问题则令 @errs +1 
      update Bill set Balance=Balance +@Amount where ID =@IDTo
      set @errs =@errs +@@ERROR   --若上一句 update 语句有问题则令 @errs +1 
      if @errs =0  --当两条 update 语句无问题时 提交 事务
        begin
          commit tran
          print '转账成功'
        end
      else  --否则 回滚
        rollback tran
    end
  else
    print '转账失败'

:由于全局变量@@ERROR 存储的永远只是上一句SQL语句执行的情况,当执行正常时返回0,否则返回不等于0的值,而再执行一句SQL语句时,执行状态会覆盖上一次的状态吗,因此要用@errs来累计每一句SQL语句的状态值。

    4、执行存储过程,小龙向小李转账 2000

exec Transfer '9FCCA201-8807-4ADA-A482-C63FD8003E54', 'C3A1A21D-3277-4962-AE97-E4C5F8B7F2EB', 2000

     显示转账成功,如图

SqlServer——事务一编程进阶(SqlServer技术内幕 T-SQL程序设计 第九章

运行  select * from Bill

如图:

SqlServer——事务一编程进阶(SqlServer技术内幕 T-SQL程序设计 第九章

再运行    exec Transfer '9FCCA201-8807-4ADA-A482-C63FD8003E54', 'C3A1A21D-3277-4962-AE97-E4C5F8B7F2EB', 2000

此时由于小龙余额只有2000,达不到要求的转账2000后余额要大于1000的要求,所以转账失败,如图:

SqlServer——事务一编程进阶(SqlServer技术内幕 T-SQL程序设计 第九章

二、begin try……end try     begin catch……end catch  方式。

在begin try……end try写事务中的执行代码并在最后提交事务,当执行SQL语句出现问题时,会跳转到  begin catch……end catch 中,因此可以在 begin catch……end catch 中写回滚事务。

  我们以订单号的例子来看一下 begin try……end try 的用法(每次获取最大的号,并+1)

    1、建表并插入数据如下:

create table Orders
(
  name varchar(10),
  OrdersID int,
)
insert into Orders values('订单号',1000)

  2、新建查询,代码如下

  begin tran 
  declare @orderID int
  declare @n int=0
  begin try
    while @n<20  --循环20次
    begin
      select @orderID =max(Orders .OrdersID) from Orders with(xlock,rowlock)
      set @orderID=@orderID +1
      print '链接 1 :'+CONVERT(nvarchar(20),@orderID)
      waitfor delay '00:00:01'
      insert into Orders values('链接 1 ',@orderID) 
      set @n=@n+1
    end
    commit tran
  end try
  begin catch
    rollback tran
  end catch

  select * from Orders

  3、再次新建查询,代码与 2 相似,将'链接 1 '改为'链接 2 ' 代码如下

begin tran 
  declare @orderID int
  declare @n int=0
  begin try
    while @n<20  --循环20次
    begin
      select @orderID =max(Orders .OrdersID) from Orders with(xlock,rowlock)
      set @orderID=@orderID +1
      print '链接 2 :'+CONVERT(nvarchar(20),@orderID)
      waitfor delay '00:00:01'
      insert into Orders values('链接 2 ',@orderID) 
      set @n=@n+1
    end
    commit tran
  end try
  begin catch
    rollback tran
  end catch

  select * from Orders

  4、再次新建查询,将'链接 1 '改为'链接 3 ' 。当然新建查询越多越好,以模拟高并发。并将3个新建查询中代码执行,结果如图,发现号码并没有丢失。

SqlServer——事务一编程进阶(SqlServer技术内幕 T-SQL程序设计 第九章

附:锁的兼容性表

现有的授权模式

SqlServer——事务一编程进阶(SqlServer技术内幕 T-SQL程序设计 第九章

链接: http://www.cnblogs.com/guanjie20/archive/2013/02/17/2914488.html