Xact_Abort


SQL Server has an equivalent to the On Error Go To command used by Visual Basic (before .NET). The Set Xact_Abort On statement forces SQL Server to roll back the complete transaction and stop further processing on the occurrence of any error. For example, execute the following script that attempts to insert one integer and one date into the integer column:

      create table test (i int)      GO      set xact abort on      begin tran      print 'Begin'      insert test(i) values(1)      insert test(i) values('5/5/5')      commit tran      print 'End'      GO      select * from test 

SQL Server will detect the problem and automatically roll back the transaction:

      Begin      (1 row(s) affected)      Msg 245, Level 16, State 1, Line 6      Syntax error converting the varchar value '5/5/5' to a column      of data type int.      i      -----------      (0 row(s) affected) 

Unfortunately, this solution could present a potential problem. This statement will also completely stop execution of the current batch. The error can still be detected and handled from the client application, but inside the Transact-SQL code, SQL Server will treat it as a fatal error. Note that the second batch in the preceding example (the Select statement) was started and executed as though nothing had happened.

However, the Try-Catch statement can prevent batches from being terminated. In the following example, I wrapped the two Insert statements in an error handler:

      set xact_abort on      Begin try            begin tran            print 'Begin try'         insert test(i) values(1)         insert test(i) values('5/5/5')         commit tran         print 'End try'      End try      Begin Catch         rollback tran         print 'Catch'      End Catch      select * from test 

Note that I added the Rollback statement to the Catch block. You will get the following text in the Results pane:

      Begin try      (1 row(s) affected)      Catch      i      ----------      (0 row(s) affected) 

Error Handling Architecture: Based on Set Xact_Abort On

You can take advantage of the fact that Set Xact_Abort On without a Try-Catch statement stops further execution. You can decide to write all of your stored procedures based on this behavior:

      create Procedure dbo.ap_LeasedAsset_Insert7      -- Insert leased asset and update total in LeaseSchedule.      -- (demonstration of SET XACT_ABORT ON solution)                 (                 @intEqId int,                 @intLocationId int,                 @intStatusId int,                 @intLeaseId int,                 @intLeaseScheduleId int,                 @intOwnerId int,                 @mnyLease money,                 @intAcquisitionTypeID int                 )      As      set nocount on      SET XACT_ABORT ON      begin transaction      -- insert asset      insert Inventory(EqId,               LocationId,                      StatusId,            LeaseId                      LeaseScheduleId,     OwnerId,                      Lease,               AcquisitionTypeID)      values (        @intEqId,            @intLocationId,                      @intStatusId,        @intLeaseId                      @intLeaseScheduleId, @intOwnerId,                      @mnyLease,           @intAcquisitionTypeID)      -- update total      update dbo.LeaseSchedule      Set PeriodicTotalAmount = PeriodicTotalAmount + @mnyLease      where LeaseId = @intLeaseId      commit transaction      return (0) 

The burden of error handling for such procedures would be transferred to the developers of client applications.

Another problem is that the Set Xact_Abort statement does not detect "compilation" errors. According to SQL Server Books OnLine: "Compile errors, such as syntax errors, are not affected by Set Xact_Abort." Unfortunately, because of deferred name resolution, compilation errors can occur at runtime as well. By editing the stored procedure from the preceding example, the Update statement references a nonexistent table:

      -- update total      update dbo.LeaseSchedule_NON_EXISTING_TABLE      Set PeriodicTotalAmount = PeriodicTotalAmount + @mnyLease where LeaseId = @intLeaseId 

Next, run the stored procedure:

      Exec dbo.ap_LeasedAsset_Insert8                 @intEqId = 100,                 @intLocationId = 1,                 @intStatusId = 1,                 @intLeaseId = 1,                 @intLeaseScheduleId = 1,                 @intOwnerId = 1,                 @mnyLease = 5000,                 @intAcquisitionTypeID = 1      -- test transaction      select *      from Inventory      where EqId = 100      and LocationId = 1 

SQL Server simply stops the execution of the stored procedure without a rollback:

      Server: Msg 208, Level 16, State 1, Procedure prInsertLeasedAsset_8, Line 30      Invalid object name 'LeaseSchedule_NON_EXISTING_TABLE'.      Server: Msg 266, Level 16, State 1, Procedure prInsertLeasedAsset_8, Line 36      Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK      TRANSACTION statement is missing. Previous count = 0, current count = 1.      EqId        LocationId  StatusId Lease      --------------------------------------------      100         1           1        5000.0000 

This is a potentially significant problem. The official response concerning my support question on this matter was that SQL Server is behaving as specified in SQL Server Books OnLine. Developers have different expectations—in the case of an error, the transaction should be rolled back. This explanation makes it sound like the Set Xact_Abort statement is useless. Fortunately, the stored procedure will be promoted to production only after detailed unit testing, and therefore it should not reference nonexistent tables.

However, there is an additional problem: only the stored procedure with the syntax error is aborted. Assume that the stored procedure is executed as a nested stored procedure and that the compilation error occurs in the inner stored procedure. The earlier procedure is split into two procedures to demonstrate this scenario:

      Create Procedure prUpdateLeaseSchedule         @intLeaseId int,         @mnyLease int      as      update LeaseSchedule_NON_EXISTING_TABLE      set PeriodicTotalAmount = PeriodicTotalAmount + @mnyLease      where LeaseId = @intLeaseId      return (0)      GO      create Procedure pr!nsertLeasedAsset_9      -- Insert leased asset and update total in LeaseSchedule.      -- (demonstration of compilation error in nested stored procedure)                 (                 @intEqId int,                 @intLocationId int,                 @intStatusId int,                 @intLeaseId int,                 @intLeaseScheduleId int,                 @intOwnerId int,                 @mnyLease money,                 @intAcquisitionTypeID int                 )      As      set nocount on      SET XACT_ABORT ON      begin transaction     -- insert asset      insert Inventory(EqId,                LocationId,                       StatusId,            LeaseId                       LeaseScheduleId,     OwnerId,                       Lease,               AcquisitionTypeID)     values (          @intEqId,     @intLocationId,                       @intStatusId,        @intLeaseId                       @intLeaseScheduleId, @intOwnerId,                       @mnyLease,           @intAcquisitionTypeID)      -- update total      exec prUpdateLeaseSchedule (SintLeaseId @mnyLease      commit transaction      return (0)      GO 

Now run them:

      Exec prInsertLeasedAsset_9                 @intEqId = 200,                 @intLocationId = 1,                 @intStatusId = 1,                 @intLeaseId = 1,                 @intLeaseScheduleId = 1,                 @intOwnerId = 1,                 @mnyLease = 5000,                 @intAcquisitionTypeID = 1      -- test transaction      select EqId, LocationId, StatusId, Lease      from Inventory      where EqId = 200      and LocationId = 1 

SQL Server simply stops the execution of the inner stored procedure, but the outer stored procedure continues as though nothing has happened (and even commits the transaction):

      Server: Msg 208, Level 16, State 1, Procedure prUpdateLeaseSchedule, Line 5      Invalid object name 'LeaseSchedule_NON_EXISTING_TABLE'.      EqId        LocationId  StatusId Lease      -------------------------------------------------      200         1           1        5000.0000 

At the time, my expectation was that the Set Xact_Abort statement would abort further execution of everything, as it does in the case of runtime errors. Unfortunately, it does not behave in that way. This is potentially very dangerous, but as I said before, problems such as this should be caught during the QA phase.

Error Handling Architecture: Xact_Abort + No Transaction Nesting

On a recent .NET project I was involved with, there were many errors when we mixed different types of transactions—COM+, DTC, ADO, and Transact-SQL. Therefore, we decided not to mix them. We went even further and decided not to nest any transactions. If the caller initiates a transaction, the nested procedure skips its own initiation of the transaction. Furthermore, the transaction should be closed only from within the procedure that initiated it. We also decided, as a rule, not to handle errors in stored procedures. The rationale was that SQL Server cannot trap all errors and that we need to write error handling in the client application (actually the middleware components) anyway.

The following procedure records the number of opened transactions on entry. The Begin Tran statement is preceded by the If statement that initiates the transaction only if the procedure is not already processing a transaction:

      create procedure dbo.ap_Equipment_Insert      -- insert equipment (and if necessary equipment type)      -- (demonstration of alternative method for error handling and transaction      processing)      OchvMake varchar(SO),      OchvModel varchar(SO),      OchvEqType varchar(SO),      OintEqId int OUTPUT      AS      set xact_abort on      set nocount on      declare @intTrancountOnEntry int,              @intEqTypeId int      set @intTrancountOnEntry = @@tranCount      -- does such EqType already exist in the database      If not exists (Select EqTypeId From dbo.EqType                     Where EqType = @chvEqType)      --if it does not exist      Begin          if @@tranCount = 0             BEGIN TRAN          -- insert new EqType in the database          Insert dbo.EqType (EqType)          Values (@chvEqType)          -- get id of record that you've just inserted          Select @intEqTypeId = @@identity       End       else       begin          -- read Id of EqType          Select @intEqTypeId          From dbo.EqType          Where EqType = OchvEqType       end       --insert equipment       Insert dbo.Equipment (Make, Model, EqTypeId)       Values (@chvMake, @chvModel, @intEqTypeId)       Select @intEqId = @@identity       if @@tranCount < @intTrancountOnEntry           COMMIT TRAN       return 0 

The Commit Tran statement will similarly be executed only if the transaction is initiated in the current procedure.

The following procedure demonstrates the way to return logic errors to the caller. Notice that I am using both Raiserror and Return statements. It is very important to use the Return statement to communicate an error to the caller because the caller might not be able to detect the effect of the Raiserror statement.

      ALTER Procedure dbo.ap_Inventory_InsertXA      -- insert inventory record , update inventory count and return Id      -- (demonstration of alternative method for error handling      -- and transaction processing)         @intEqId int,         @intLocationId int,         @inyStatusId tinyint,         @intLeaseId int,         @intLeaseScheduleId int,         @intOwnerId int,         @mnsRent smallmoney,         @mnsLease smallmoney,         @mnsCost smallmoney,         @inyAcquisitionTypeID int,         @intInventoryId int output      As      declare (SintTrancountOnEntry int      set nocount on      set xact_abort on      set @intTrancountOnEntry = @@tranCount      if @@tranCount = 0         begin tran      Insert into dbo.Inventory (EqId, LocationId, StatusId,               LeaseId LeaseScheduleId, OwnerId,               Rent, Lease, Cost,               AcquisitionTypeID)      values (@intEqId, @intLocationId, @inyStatusId,              @intLeaseId @intLeaseScheduleId, @intOwnerId,              @mnsRent, @mnsLease, @mnsCost,              @inyAcquisitionTypeID)           select @intInventoryId = Scope_Identity()           update dbo.InventoryCount           Set InvCount = InvCount + 1           where LocationId = @intLocationId           if @@rowcount <> 1           begin               -- business error               Raiserror(50133, 16, 1)               if @@tranCount > @intTrancountOnEntry                  rollback tran               return 50133           end           if @@tranCount > @intTrancountOnEntry              commit tran           return 0 

The following procedure demonstrates the detection of logic errors from the nested stored procedure: create procedure dbo.ap_InventoryEquipment_Insert_XA

      - - insert new inventory and new equipment      -- (demonstration of alternative method for error handling      -- and transaction processing)        (SchvMake varchar(50),         @chvModel varchar(50),         @chvEqType varchar(30),         @intLocationId int,         @inyStatusId tinyint,         @intLeaseId int,         @intLeaseScheduleId int,         @intOwnerId int,         @mnsRent smallmoney,         @mnsLease smallmoney,         @mnsCost smallmoney,         @inyAcquisitionTypeID int,         @intlnventoryId int output,         @intEqId int output      as      Set nocount on      set xact_abort on      declare @intError int,              @intTrancountOnEntry int      set @intError = 0      set @intTrancountOnEntry = @@tranCount      if @@tranCount = 0          begin tran      -- is equipment already in the database      if not exists(select EqId                   from Equipment                   where Make = @chvMake                   and Model = @chvModel)      EXEC @intError = dbo. ap_Equipment_Insert @chvMake, @chvModel, @chvEqType,                                              @intEqId OUTPUT      if @intError > 0      begin           if @@tranCount > @intTrancountOnEntry               rollback tran           return @intError      end      exec @intError = dbo. ap_Inventory_InsertXA            @intEqId,                @intLocationId,       @inyStatusId,            @intLeaseId              @intLeaseScheduleId,  @intOwnerId,            @mnsRent,                @mnsLease,            @mnsCost,            @inyAcquisitionTypeID, @intInventoryId output      if @intError > 0      begin          if @@tranCount > @intTrancountOnEntry              ROLLBACK TRAN          return @intError      end      if @@tranCount @ @intTrancountOnEntry          COMMIT TRAN      return 0 

If an error has been returned, the current stored procedure will roll back the transaction (using Rollback Transaction) if a transaction has been initiated in it. The caller stored procedure can also be designed so that it knows about all (or some) error codes that can be returned from a nested stored procedure. Then it is possible to write code that will handle the errors.

To test it, run the following:

      declare @intError int,              @intInvId int,              @intEqId int      begin tran      exec @intError = ap_InventoryEquipment_Insert_XA           @chvMake = 'Compaq',           @chvModel = 'IPaq 3835',           @chvEqType = 'PDA',           @intLocationId = 12,           @inyStatusId = 1,           @intLeaseId = null,           @intLeaseScheduleId = 1,           @intOwnerId = 411,           @mnsRent = null,           @mnsLease = null,           @mnsCost = $650,           @inyAcquisitionTypeID = 1,           @intlnventoryId = @intlnvId output,           @intEqId = @intEqId output      if @intError = 0         commit tran      else          rollback tran      select @intError Err      select * from Inventory where InventoryId = @intInvId      select * from Equipment where EqId = @intEqId 

In the case of an error, SQL Server returns the error message and rolls back the transaction:

      Server: Mag 50133, Level 16, State 1, Procedure ap Inventory InsertXA, Line 48      Unable to update inventory count.      Err      ---------      50133      (1 row(s) affected)      Inventoryid EqId          LocationId StatusId LeaseId LeaseScheduleId      ----------- ------------  ---------- -------- ------- ---------------      (0 row(s) affected)       EqId      Make                                                  Model       --------- ----------------------------------------------------- -----      (0 row(s) affected) 




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net