In this article I want to tell you about Transaction Isolation Level in
SQL Server 6.5 and SQL Server 7.0, what kind of Transaction Isolation Level
exists, and how you can set the appropriate Transaction Isolation Level.
There are four isolation levels:
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
SQL Server 6.5 supports all of these Transaction Isolation Levels, but has
only three different behaviors, because in SQL Server 6.5 REPEATABLE READ
and SERIALIZABLE are synonyms. It because SQL Server 6.5 supports only
page locking (there is no full support of row locking as in SQL Server 7.0)
and if REPEATABLE READ isolation level was set, then another transaction
cannot insert the row before the first transaction was finished, because
page will be locked. So there are no phantoms in SQL Server 6.5, if
REPEATABLE READ isolation level was set.
SQL Server 7.0 supports all of these Transaction Isolation Levels and can
separate REPEATABLE READ and SERIALIZABLE.
Let me to describe each isolation level.
READ UNCOMMITTED
When it’s used, SQL Server not issue shared locks while reading data. So,
you
can read an uncommitted transaction that might get rolled back later. This
isolation level is also called dirty read. This is the lowest isolation
level.
It ensures only that a physically corrupt data will not be read.
READ COMMITTED
This is the default isolation level in SQL Server. When it’s used, SQL
Server
will use shared locks while reading data. It ensures that a physically
corrupt
data will not be read and will never read data that another application has
changed and not yet committed, but it not ensures that the data will not be
changed before the end of the transaction.
REPEATABLE READ
When it’s used, then dirty reads and nonrepeatable reads cannot occur.
It means that locks will be placed on all data that is used in a query,
and another transactions cannot update the data.
This is the definition of nonrepeatable read from SQL Server Books Online:
nonrepeatable read When a transaction reads the same row more than one time, and between the two (or more) reads, a separate transaction modifies that row. Because the row was modified between reads within the same transaction, each read produces different values, which introduces inconsistency. |
|---|
SERIALIZABLE
Most restrictive isolation level. When it’s used, then phantom values cannot
occur. It prevents other users from updating or inserting rows into the data
set until the transaction is complete.
This is the definition of phantom from SQL Server Books Online:
phantom Phantom behavior occurs when a transaction attempts to select a row that does not exist and a second transaction inserts the row before the first transaction finishes. If the row is inserted, the row appears as a phantom to the first transaction, inconsistently appearing and disappearing. |
|---|
You can set the appropriate isolation level for an entire SQL Server session
with the SET TRANSACTION ISOLATION LEVEL statement.
This is the syntax from SQL Server Books Online:
SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE } |
|---|
You can use DBCC USEROPTIONS command to determine the Transaction Isolation
Level currently set. This command returns the set options that are active
for the current connection. This is the example:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO DBCC USEROPTIONS GO |
|---|
These are the results:
Set Option Value —————————— ———————————— textsize 64512 language us_english dateformat mdy datefirst 7 isolation level read uncommitted |
|---|