SQL Server provides a number of hints that you can use in a query to override the default transaction isolation level:
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:
declare @seqno intbegin transaction-- get a UNIQUE sequence number from sequence tableSELECT @seqno = isnull(seq#,0) + 1from sequence WITH (HOLDLOCK) ...