在存储过程中编写正确的事务处理代码

在 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
posted on 2013-12-28 17:03  davidkam  阅读(195)  评论(0编辑  收藏  举报