Customizing Transaction Isolation Level

By default, Microsoft® SQL Server™ 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 session’s isolation level 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 specifically 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

(5 rows affected)

DBCC execution completed. If DBCC printed error messages, see your System Administrator.

See Also