Microsoft® SQL Server 2012 Unleashed

Transaction Isolation-Level Hints

SQL Server provides a number of hints that you can use in a query to override the default transaction isolation level:

HOLDLOCK—HOLDLOCK maintains shared locks for the duration of the entire statement or for the entire transaction, if the statement is in a transaction. This option is equivalent to the Serializable Read isolation level. The following hypothetical example demonstrates the usage of the HOLDLOCK statement within a transaction:

Click here to view code image

declare @seqno intbegin transaction-- get a UNIQUE sequence number from sequence tableSELECT @seqno = isnull(seq#,0) + 1from sequence WITH (HOLDLOCK) ...