Export (0) Print
Expand All

Transaction Savepoints

SQL Server 2000

  New Information - SQL Server 2000 SP3.

Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement, and then later execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of a transaction.

Savepoints are useful in situations where errors are unlikely to occur. The use of a savepoint to roll back part of a transaction in the case of an infrequent error can be more efficient than having each transaction test to see if an update is valid before making the update. Updates and rollbacks are expensive operations, so savepoints are effective only if the probability of encountering the error is low and the cost of checking the validity of an update beforehand is relatively high.

This example shows the use of a savepoint in an order system in which there is a low probability of running out of stock because the company has effective suppliers and reorder points. Usually an application would verify that there is enough stock on hand before attempting to make the updates that would record the order. This example assumes that, for some reason (such as connecting over a slow modem or WAN), first verifying the quantity of stock available is relatively expensive. The application could be coded to just make the update, and if it gets an error indicating that there is not enough stock, it rolls back the update. In this case, a quick check of @@ERROR after the insert is much faster than verifying the amount before the update.

The InvCtrl table has a CHECK constraint that triggers a 547 error if the QtyInStk column goes below 0. The OrderStock procedure creates a savepoint. If a 547 error occurs, it rolls back to the savepoint and returns the number of items on hand to the calling process. The calling process can then decide if it wants to replace the order for the quantity on hand. If OrderStock returns a 0, the calling process knows there was enough stock on hand to satisfy the order.

Security Note  Validate all user input. Do not concatenate user input before validating it. Never execute a command constructed from unvalidated user input. For more information, see Validating User Input.

SET NOCOUNT OFF
GO
USE pubs
GO
CREATE TABLE InvCtrl
      (WhrhousID      int,
      PartNmbr      int,
      QtyInStk      int,
      ReordrPt      int,
      CONSTRAINT InvPK PRIMARY KEY
      (WhrhousID, PartNmbr),
      CONSTRAINT QtyStkCheck CHECK (QtyInStk > 0) )
GO
CREATE PROCEDURE OrderStock @WhrhousID int, @PartNmbr int,
            @OrderQty int
AS
DECLARE @ErrorVar int
SAVE TRANSACTION StkOrdTrn
UPDATE InvCtrl SET QtyInStk = QtyInStk - @OrderQty
WHERE WhrhousID = 1
   AND PartNmbr = 1
SELECT @ErrorVar = @@error
IF (@ErrorVar = 547)
BEGIN
   ROLLBACK TRANSACTION StkOrdTrn
   RETURN (SELECT QtyInStk
          FROM InvCtrl
          WHERE WhrhousID = @WhrhousID
           AND PartNmbr = @PartNmbr)
END
ELSE
   RETURN 0
GO

See Also

ROLLBACK TRANSACTION

SAVE TRANSACTION

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft