在存储过程中编写正确的事务处理代码(SQL Server 2000 & 2005)

在 SQL Server 中数据库事务处理是个重要的概念,也稍微有些不容易理解,很多 SQL 初学者编写的事务处理代码存往往存在漏洞,本文介绍了三种不同的方法,举例说明了如何在存储过程事务处理中编写正确的代码。

在编写 SQL Server 事务相关的存储过程代码时,经常看到下面这样的写法:

      begin tran
         update statement 1 ...
         update statement 2 ...
         delete statement 3 ...
      commit tran

 

这样编写的SQL存在很大隐患。请看下面的例子:

   create table demo(id int not null)
   go

   begin tran
      insert into demo values (null)
      insert into demo values (2)
   commit tran
   go

 

执行时会出现一个违反 not null 约束的错误信息,但随后又提示(1 row(s) affected)。 我们执行 select * from demo 后发现 insert into demo values(2) 却执行成功了。 这是什么原因呢? 原来 sql server 在发生 runtime 错误时,默认会 rollback 引起错误的语句,而继续执行后续语句。

如何避免这样的问题呢?有三种方法:

1. 在事务语句最前面加上set xact_abort on

   set xact_abort on

   begin tran
      update statement 1 ...
      update statement 2 ...
      delete statement 3 ...
   commit tran
   go

 

当 xact_abort 选项为 on 时,sql server 在遇到错误时会终止执行并 rollback 整个事务。

2. 在每个单独的DML语句执行后,立即判断执行状态,并做相应处理。

   begin tran
      update statement 1 ...

      if @@error <> 0 begin
         rollback tran
         goto labend
      end

      delete statement 2 ...

      if @@error <> 0 begin
         rollback tran
         goto labend
      end

   commit tran
      labend:
   go

 

3. 在SQL Server 2005中,可利用 try...catch 异常处理机制

   begin tran

   begin try
      update statement 1 ...
      delete statement 2 ...
   end try
   begin catch
      if @@trancount > 0
         rollback tran
   end catch

   if @@trancount > 0
      commit tran
   go

 

下面是个简单的存储过程,演示事务处理过程。

   create procedure dbo.pr_tran_inproc
   as
   begin
      set nocount on

      begin tran
         update statement 1 ...

         if @@error <> 0 begin
            rollback tran
            return -1
         end

         delete statement 2 ...

         if @@error <> 0 begin
            rollback tran
            return -1
         end

      commit tran

      return 0
   end
   go
原文地址:http://www.sqlstudy.com/sql_article.php?id=2008060701
©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值