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

    Requires Free Membership to View

  • 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!

This was first published in March 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.