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

如图：

　　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

　　　　　显示转账成功，如图

运行  select * from Bill

如图：

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

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

 

二、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个新建查询中代码执行，结果如图，发现号码并没有丢失。

 

 

附：锁的兼容性表

现有的授权模式

 

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