Serializable Table Hint in SQL Server

Equivalent to HOLDLOCK. Makes shared locks more restrictive by holding them until a transaction is completed, instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether the transaction has been completed or not. The scan is performed with the same semantics as a transaction running at the SERIALIZABLE isolation level

This hint specifies that the Database Engine locks the entire range of key values that qualify for the statements of the transaction. This prevents other transactions from updating or inserting rows that would qualify for any of the statements in the transaction. In simpler terms, it ensures that no other transaction can change the data that the current transaction is working with until it’s finished, ensuring a consistent view of the data.

Syntex

SELECT * FROM TableName WITH (SERIALIZABLE) WHERE Condition;

SERIALIZABLE isolation level prevents:

Ø  Dirty reads: Reading uncommitted changes.

Ø  Non-repeatable reads: Different results within the same transaction when reading the same data multiple times.

Ø  Phantom reads: New rows appearing in subsequent reads within the same transaction.

The table hint is applied using the WITH clause in a query. It places range locks on the data, which can reduce concurrency and increase the risk of deadlocks, so use it judiciously.