Procedures and Transactions

The behavior of transactions regarding stored procedures forces you to handle transactions in a particular way within your procs. Consider this procedure and the batch that executes it. First, we’ll set up the test by creating two tables and adding a couple of rows.

create  table  orders         -- create an orders table
(order_no   int  identity,
  item_no  int  not  null,
  qty  int  not  null)
go
create  table  inventory      -- and an inventory table
(item_no  int  not  null,
   descr  varchar(10)  not  null,
   stock_qty  int  not  null)
go
                              -- add some rows to inventory
insert  inventory  (item_no,  descr,  stock_qty)
values  (1, "boots",  10)
insert  inventory  (item_no,  descr,  stock_qty)
values  (2, "hats",  20)
go

Here’s the procedure:

create procedure order_ins -- a procedure to insert orders
  (@item_no int,
   @qty     int)
as
declare @in_stock int
begin tran
    update inventory        -- deplete inventory first
       set @in_stock = stock_qty = stock_qty - @qty
     where item_no = @item_no
    if @in_stock < 0        -- check stock now
    begin
       rollback tran        -- rollback the change if short
       raiserror (“insufficient stock”, 16, 1)
    end
                            -- add the order
    insert orders ( item_no,  qty)
         values (@item_no, @qty)
 commit tran                -- commit the transaction
 return
 go

Notice that the procedure updates the inventory first. (The T-SQL extension to the UPDATE statement comes in handy here: you can retrieve the new value for the stock_qty at the same time that you set it in the table.) It’s usually better to set the value first and then check it. This gives you a persistent page lock on the modified page in inventory.

If the new stock level is too low, you can roll back the transaction at this point, raising an error and returning a non-zero status. Otherwise, the row is inserted and the transaction committed. Notice that this example lacks some necessary error checking (for the sake of brevity) and will fail if no qualifying rows are found in the UPDATE statement.

You need a transaction within the procedure to connect the contents of the inventory and orders tables. The transaction mechanism also provides automated locking to ensure the consistency of the changes to the various tables.

Let’s test the procedure by adding an order for three pairs of boots:

order_ins  1,  3
select  *  from inventory
select  *  from orders

item_no       descr          stock_qty
----------    ----------     ----------
1             boots          7
2             hats           20
order_no      item_no        qty
----------    ----------     ----------
1             1             3

The order has been placed and stock was depleted properly.

Problems with Nested Transactions

Let’s run the same procedure several times to insert several rows within a transaction:

begin  tran
       execute order_ins 1, 2
       execute order_ins 2, 4
commit  tran
select *  from inventory
select *  from orders

item_no       descr          stock_qty
----------    ----------     ----------
1             boots          5
2             hats           16
order_no      item_no        qty
----------    ----------     ----------
1             1              3
2             1              2
3             2              4

The transaction is fine as long as nothing fails inside a procedure. What happens if one of the procedures fails? Let’s force the first procedure to fail by trying to sell more boots (6 pairs) than are left in stock (only 5 pairs):

begin  tran
       execute order_ins 1, 6
       execute order_ins 2, 4
commit  tran
select *  from inventory
select *  from orders

Msg  50000,  Level  16,  State  1
insufficient    stock
Msg  266,  Level  16,  State  1
Transaction  count  after  EXECUTE  indicates
that  a  COMMIT  or  ROLLBACK  TRAN  is  missing.
Previous  count  =  1,  Current  count  =  0.
Msg  3902,  Level  16,  State  1
The commit transaction request has no corresponding BEGIN TRANSACTION.
item_no      descr          stock_qty
-------      ----------     ----------
1            boots          5
2            hats           12
order_no     item_no        qty
--------     ----------     ----------
1            1              3
2            1              2
3            2              4
4            2              4

First of all, notice that only half of our order made it to the server. Instead of selling both boots and hats, only the hats were sold (see order_no 4). To understand how only half of a transaction made it to the server, you need to examine the error messages that were transmitted.

Msg  50000,  Level  16,  State  1
insufficient    stock

This is our error message from the stored procedure RAISERROR statement. It warned us that the procedure had failed.

Msg  266,  Level  16,  State  1
Transaction  count  after  EXECUTE  indicates
that  a  COMMIT  or  ROLLBACK  TRAN  is  missing.
Previous  count  =  1,  Current  count  =  0.

This error message warns us that the procedure changed the status of the global variable, @@trancount . As you learned on Day 13, @@trancount tracks the transaction nesting level of your session. (Each execution of BEGIN TRAN increments @@trancount , each COMMIT TRAN decrements it, and ROLLBACK TRAN sets it to 0.) When the procedure starts, the value of @@trancount is already 1 because of the BEGIN TRAN statement in the batch. When the procedure executes a ROLLBACK TRAN statement, it resets @@trancount to 0 before the procedure returns. This change in transaction nesting level triggers a non-fatal warning message, which is a useful flag to you that transaction control is failing.

Msg 3902, Level 16, State 1
The commit transaction request has no corresponding BEGIN TRANSACTION.

Here’s another non-fatal warning that the transaction control is out of whack. @@trancount is already 0 when the procedure exits. The COMMIT TRAN statement in the batch tries to decrement its value, but @@trancount cannot be negative.