Monday, June 7, 2010

ADS 10 Tip #6 – Nested Transactions

Nesting transactions is the ability to start a transaction within a transaction. This allows for simpler encapsulation since you can start a transaction within your function or procedure even if the function or procedure was called within an active transaction.

Starting with version 9 you could create transaction save points which provided similar functionality. However, this required you to check to see if you were in a transaction prior to starting a transaction. Version 10 supports nested transactions allowing you to begin a transaction within an active transaction. Each new transaction will increment the transaction nesting level. The entire transaction will be committed when the nesting level reaches zero.

Calling rollback within any nesting level will rollback the entire transaction. If you need to provide partial rollback functionality you will need to use savepoints. Below is an SQL script example.

DECLARE cOrder CURSOR AS EXECUTE PROCEDURE CreateInvoice( 141, 10010 );
DECLARE @OrdID CHAR(36);
DECLARE @Price MONEY;

BEGIN TRANSACTION;  // Nesting level = 1

// Opening the cursor creates the order
OPEN cOrder;
FETCH cOrder;
@OrdID = cOrder.OrderID;

  // Add some items to the order
  BEGIN TRANSACTION; // Nesting level = 2
	EXECUTE PROCEDURE AddInvoiceItem( @OrdID, 'SP080-50', 25 );
	EXECUTE PROCEDURE AddInvoiceItem( @OrdID, 'SP060-43', 15 );
	EXECUTE PROCEDURE AddInvoiceItem( @OrdID, 'SP075-30', 20 );
	EXECUTE PROCEDURE AddInvoiceItem( @OrdID, 'SP040-15', 30 );
  COMMIT WORK; // Nesting level = 1

// The ProcessOrder stored procedure uses a transaction
EXECUTE PROCEDURE ProcessOrder( @OrdID );

COMMIT WORK; // Nesting level = 0

You can view a screencast on the new Transaction Processing features on the DevZone.

No comments: