--Transaction Savepoint Example -- Rollback to save point if there is not enough stock in the warehouse to fulfill -- an order. USE pubs GO CREATE TABLE InventoryControl ( WarehouseID INT, PartNumber INT, QtyInStock INT, ReorderPoint INT, CONSTRAINT InventoryControlPK PRIMARY KEY (WarehouseID, PartNumber), CONSTRAINT QtyStockCHK CHECK (QtyInStock > 0) ) GO CREATE PROC OrderStock @WarehouseID INT, @PartNumber INT, @OrderQty INT AS DECLARE @ErrorVar INT SAVE TRANSACTION StockOrderTrans UPDATE InventoryControl SET QtyInStock = QtyInStock - @OrderQty WHERE WarehouseID = @WarehouseID AND PartNumber = @PartNumber -- Check constraint QtyStockCHK will raise 547 error if violated. SELECT @ErrorVar = @@ERROR IF (@ErrorVar = 547) BEGIN ROLLBACK TRANSACTION StockOrderTrans RETURN ( SELECT QtyInStock FROM InventoryControl WHERE WarehouseID = @WarehouseID AND PartNumber = @PartNumber ) END ELSE RETURN 0 GO LOCKING COMPATIBILITY MATRIX: Existing Locks ========================== IS S U IX SIX X Requested Locks =============== Intent Shared (IS) Y Y Y Y Y N Shared (S) Y Y Y N N N Update (U) Y Y N N N N Intent Exclusive (IX) Y N N Y N N Shared with Intent Exclusive (SIX) Y N N N N N Exclusive (X) N N N N N N (axis of symmetry from top L corner to bottom R)