More About Transactions and Composite Transact-SQL Constructs - Savepoints
(Page 2 of 6 )
SQL Server contains a mechanism for rolling back only part of a transaction. This statement may seem to contradict the basic idea of a SQL Server transaction as I have explained it, but it can be justified in some cases. Microsoft recommends savepoints be used if it is more expensive to check whether the change will be valid in advance (for example, because of a slow connection) and when the operation has a high probability of success. For example, assume that you are trying to reserve a set of plane tickets (or to get a set of some other resources) using different companies (distributed database system). Each leg of a journey has to be booked separately. If the reservation fails, you will roll back just that leg of the journey, not all the reservations that you already successfully made. Only in the case that it is impossible to find any alternative for the remaining part of the journey will you roll back the complete transaction.
To mark a savepoint in a transaction, use the following statement:
Save Tran[saction]{savepoint_name|@savepoint_variable}
The savepoint’s name is also a SQL Server identifier, but SQL Server reads only the first 32 characters.
To roll back part of the transaction, you must use the savepoint name or variable:
Rollback Tran[saction]{savepoint_name|@savepoint_variable}
NOTE
Rollback Transaction statements without a savepoint will roll back the complete transaction.
Savepoints do not save anything to the database. They just mark the point to which you can roll back a transaction. Resources (like locks) also stay in place after a Save Transaction statement. They are released only when a transaction has been completed or canceled.
The following procedures are designed to store an order and a set of order items in a database. The ap_ScrapOrderItem_Save stored procedure uses savepoints to roll back the insertion of a particular item.
Create Procedure dbo.ap_ScrapOrder_Save
-- save order information.
@dtsOrderDate smalldatetime,
@intRequestedById int,
@dtsTargetDate smalldatetime,
@chvNote varchar(200),
@insOrderTypeId smallint,
@inyOrderStatusId tinyint
As
Set nocount on
Insert dbo.[Order](OrderDate, RequestedById,
TargetDate, Note,
OrderTypeId, OrderStatusId)
Values (@dtsOrderDate, | @intRequestedById, |
@intRequestedById, | @chvNote, |
@insOrderTypeId, | @inyOrderStatusId) |
Return @@identity
Go
Create Procedure dbo.ap_ScrapOrderItem_Save -- Saves order item.
-- If error occurs, this item will be rolled back,
-- but other items will be saved.
-- demonstration of use of Save Transaction -- must be called from sp or batch that initiates transaction
@intOrderId int,
@intInventoryId int,
@intOrderItemId int OUTPUT
As
Set nocount on
Declare @intErrorCode int,
@chvInventoryId varchar(10)
-- name the transaction savepoint
Set @chvInventoryId = Convert(varchar, @intInventoryId)
Save Transaction @chvInventoryId
-- Set value of Lease of all equipment associated
-- with expired Lease Schedule to 0
Insert dbo.OrderItem (OrderId, InventoryId)
Values (@intOrderId, @intInventoryId)
Select @intOrderItemId = @@identity,
@intErrorCode = @@Error
If @intErrorCode <> 0
Begin
Rollback Transaction @chvInventoryId
Return @intErrorCode
End
Return 0
Go
Let’s assume that the caller is some external application that is trying to fulfill an order by adding line item by line item. If one line item fails, the application will detect an error, roll back to the last savepoint, and try to add some other line item.
The stored procedures are designed in such a manner that a transaction must be initiated by the caller. You can test the stored procedures by using the following batch:
Declare @intOrderId int,
@intOrderItemId int
Begin Tran
Exec @intOrderId = dbo.ap_ScrapOrder_Save
@dtsOrderDate = '1/10/2003',
@intRequestedById = 1,
@dtsTargetDate = '1/1/2004',
@chvNote = NULL,
@insOrderTypeId = 3, -- scrap
@inyOrderStatusId = 1 -- ordered Exec dbo.ap_ScrapOrderItem_Save
@intOrderId,
5,
@intOrderItemId OUTPUT
Exec dbo.ap_ScrapOrderItem_Save
@intOrderId,
6,
@intOrderItemId OUTPUT
Exec dbo.ap_ScrapOrderItem_Save
@intOrderId,
8,
@intOrderItemId OUTPUT
Commit Tran
In nested transaction statements, transaction names are ignored or can cause errors. If you are using transactions in stored procedures, which could be called from within other transactions, do not use transaction names. In the previous example, although stored procedures with transaction names are called from a batch (it could have been implemented as a stored procedure), the transaction itself was not nested.
Next: Locking >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter five of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.
|
|
