In addition to customizing locking with SELECT, you can also customize locking for an entire SQL Server session by setting the session's isolation level with the SET TRANSACTION ISOLATION LEVEL statement. When you set the isolation level, you specify the default locking behavior for all SELECT statements in your SQL Server session.
The following table describes the available isolation levels:
| Option | Description |
|---|---|
| READ COMMITTED | Directs SQL Server to use shared locks while reading data (the default). At this level, you cannot experience "dirty reads." |
| READ UNCOMMITTED | Directs SQL Server not to issue shared locks and does not honor exclusive locks. At this level you can experience "dirty reads," and because you can read an uncommitted transaction that might get rolled back later, you can get "phantom values" (after a rollback, the value you read logically never existed.) |
| REPEATABLE READ Or SERIALIZABLE |
Indicates that "dirty reads," nonrepeatable reads, and phantom values cannot occur. REPEATABLE READ and SERIALIZABLE are interchangeable. |
If you specify an isolation level, you can override it in individual SELECT statements by specifying the appropriate locking option with each statement (see Customizing Locking with SELECT).
Additionally, you can use DBCC USEROPTIONS to see which isolation level is set. For more information about DBCC, see DBCC statement in the Microsoft SQL Server Transact-SQL Reference.