Optimizing transactions through stored procedures

Transactions ensure that all phases of an operation across multiple systems are correctly committed before an operation can be completed. Stored procedures in general are a way to optimize nearly any operation, so transactional processing in SQL Server is best done through a stored procedure.

That said, there are certain ways to insure that transactional behavior inside a stored procedure works well. Simply encapsulating a transaction in a stored procedure is not always enough.

• The BEGIN TRANSACTION and COMMIT TRANSACTION commands should be within the stored procedure itself. Starting a transaction and then invoking a stored procedure separately creates undesirable overhead, in the form of held locks. On a server where many simultaneous transactions are being invoked, the less overhead and the less locking per transaction, the better.

• Perform setup, logic / flow control and other non-database activities outside the context of a transaction. If you have variable assignments that need to be created, for instance, do those before the transaction is invoked. If you are controlling SQL Server through an ASP page, you may want to do as much of your logic in the context of the ASP page as possible.

• Transactions in general should not be nested—i.e., invoking one transaction inside another—but this goes double for transactions invoked through stored procedures. If one transaction fails, locks on the database can wind up being held open indefinitely, with predictably bad results. One way to get around this is to use the SET LOCK_TIMEOUT <timeout_value> command to force locks to expire within a given amount of time (in milliseconds).

Serdar Yegulalp is the editor of the Windows 2000 Power Users Newsletter. Check out his Windows 2000 blog for his latest advice and musings on the world of Windows network administrators – please share your thoughts as well!