Customizing Transaction Isolation Level
By default, Microsoft® SQL Server™ 2000 operates at an isolation level of READ COMMITTED. However, an application may have to operate at a different isolation level. To make use of either more or less strict isolation levels in applications, locking can be customized for an entire session by setting the isolation level of the session with the SET TRANSACTION ISOLATION LEVEL statement.
When the isolation level is specified, the locking behavior for all SELECT statements in the SQL Server session operates at that isolation level and remains in effect until the session terminates, or until the isolation level is set to another level. For example, to set the transaction isolation level to SERIALIZABLE, ensuring that no phantom rows can be inserted by concurrent transactions into the authors table, execute:
USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT au_lname FROM authors
Note The isolation level can be overridden, if necessary, for individual SELECT statements by specifying a table-level locking hint. Specifying a table-level locking hint does not affect other statements in the session. It is recommended that table-level locking hints be used to change the default locking behavior only when absolutely necessary.
To determine the transaction isolation level currently set, use the DBCC USEROPTIONS statement, for example:
USE pubs
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
DBCC USEROPTIONS
GO
Set Option |
Value |
Textsize |
4096 |
Language |
us_english |
Dateformat |
mdy |
Datefirst |
7 |
isolation level |
repeatable read |
(5 rows affected)
DBCC execution completed. If DBCC printed error messages, see your System Administrator.