TRANSACTION ISOLATION LEVEL

SET TRANSACTION ISOLATION LEVEL

SQL Server 2000

54 out of 82 rated this helpful - Rate this topic

Controls the default transaction locking behavior for all Microsoft® SQL Server™ SELECT statements issued by a connection.

Syntax

SET TRANSACTION ISOLATION LEVEL
    { READ COMMITTED
        | READ UNCOMMITTED
        | REPEATABLE READ
        | SERIALIZABLE
    }

Arguments

READ COMMITTED

Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

READ UNCOMMITTED

Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.

REPEATABLE READ

Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.

SERIALIZABLE

Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

Remarks

Only one of the options can be set at a time, and it remains set for that connection until it is explicitly changed. This becomes the default behavior unless an optimization option is specified at the table level in the FROM clause of the statement.
The setting of SET TRANSACTION ISOLATION LEVEL is set at execute or run time and not at parse time.

Examples

This example sets the TRANSACTION ISOLATION LEVEL for the session. For each Transact-SQL statement that follows, SQL Server holds all of the shared locks until the end of the transaction.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
SELECT * FROM publishers
SELECT * FROM authors
...
COMMIT TRANSACTION

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ] 

The ISO standard defines the following isolation levels, all of which are supported by the SQL Server Database Engine:

• Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read)
• Read committed (Database Engine default level)
• Repeatable read
• Serializable (the highest level, where transactions are completely isolated from one another)
  
  Transaction Isolation level decides how is one process isolated from other process.Using
  transaction levels you can implement locking in SQL SERVER.
  There are four transaction levels in SQL SERVER :-
  1.READ COMMITTED
  The shared lock is held for the duration of the transaction, meaning that no other
  transactions can change the data at the same time. Other transactions can insert and
  modify data in the same table, however, as long as it is not locked by the first transaction.
  2.READ UNCOMMITTED
  No shared locks and no exclusive locks are honored. This is the least restrictive isolation
  level resulting in the best concurrency but the least data integrity.
  3.REPEATABLE READ
  This setting disallows dirty and non-repeatable reads. However, even though the locks
  are held on read data, new rows can still be inserted in the table, and will subsequently be
  read by the transaction.
  4.SERIALIZABLE
  This is the most restrictive setting holding shared locks on the range of data. This setting
  does not allow the insertion of new rows in the range that is locked; therefore, no phantoms
  are allowed.
  Following is the syntax for setting transaction level in SQL SERVER.
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  
  #####
   
  Normally, it's best to allow SQL Server to enforce isolation between transactions in its default manner; after all, isolation is one of the basic tenets of the ACID model . However, sometimes business requirements force database administrators to stray from the default behavior and adopt a less rigid approach to transaction isolation. To assist in such cases, SQL Server offers five different transaction isolation models. Before taking a detailed look at SQL Server's isolation models, we must first explore several of the database concurrency issues that they combat:
  
  • Dirty Reads occur when one transaction reads data written by another, uncommitted, transaction. The danger with dirty reads is that the other transaction might never commit, leaving the original transaction with "dirty" data.
  • Non-repeatable Reads occur when one transaction attempts to access the same data twice and a second transaction modifies the data between the first transaction's read attempts. This may cause the first transaction to read two different values for the same data, causing the original read to be non-repeatable
  • Phantom Reads occur when one transaction accesses a range of data more than once and a second transaction inserts or deletes rows that fall within that range between the first transaction's read attempts. This can cause "phantom" rows to appear or disappear from the first transaction's perspective.
  SQL Server's isolation models each attempt to conquer a subset of these problems, providing database administrators with a way to balance transaction isolation and business requirements. The five SQL Server isolation models are:
• The Read Committed Isolation Model is SQL Server’s default behavior. In this model, the database does not allow transactions to read data written to a table by an uncommitted transaction. This model protects against dirty reads, but provides no protection against phantom reads or non-repeatable reads.
• The Read Uncommitted Isolation Model offers essentially no isolation between transactions. Any transaction can read data written by an uncommitted transaction. This leaves the transactions vulnerable to dirty reads, phantom reads and non-repeatable reads.
• The Repeatable Read Isolation Model goes a step further than the Read Committed model by preventing transactions from writing data that was read by another transaction until the reading transaction completes. This isolation model protect against both dirty reads and non-repeatable reads.
• The Serializable Isolation Model uses range locks to prevent transactions from inserting or deleting rows in a range being read by another transaction. The Serializable model protects against all three concurrency problems.
• The Snapshot Isolation Model also protects against all three concurrency problems, but does so in a different manner. It provides each transaction with a "snapshot" of the data it requests. The transaction may then access that snapshot for all future references, eliminating the need to return to the source table for potentially dirty data.

If you need to change the isolation model in use by SQL Server, simply issue the command:

SET TRANSACTION ISOLATION LEVEL <level>

where <level> is replaced with any of the following keywords:

• READ COMMITTED
• READ UNCOMMITTED
• REPEATABLE READ
• SERIALIZABLE
• SNAPSHOT