Microsoft KB Archive/45582

INF: Writing Efficient Queries and Stored Procedures ID Number: Q45582

1.10 1.11 4.20 OS/2

Summary:

To write efficient queries and stored procedures, you should use BEGIN TRAN in read-only transactions.

HOLDLOCK causes all share locks to be held for the duration of the logical unit of work (until the commit), instead of just until the end of the SELECT statement. This can be used to prevent other users from updating the entire set of rows read by a transaction in order to provide a consistent image of the database to that transaction (Gray’s consistency level 3, or “repeatable read”).

Additional reference words: Optimization and tuning