It specifies that shared locks are held while the data is
being read by the transaction. This avoids dirty reads, but the data can be
changed before the end of the transaction. This may result in no repeatable
reads or phantom data. A phantom read takes place when a transaction is allowed
to read a row for the first read but is unable to modify the same row due to
another transaction might be performing a delete on the row in the table.
Hence, when a read is executed again within a transaction, the resultant output
shows rows missing due to the delete. This is the default level.
It specifies that statements can read rows that have been
modified by other transaction, but still to be committed. It implements dirty
read (in this scenario one process modifies data during a transaction and
another process comes in and reads data before the transaction is committed) or
isolation level 0 locking, which means that no shared locks are issued and no
exclusive locks are honored. Values in the data can be changed and rows can
appear or disappear in the data set before the end of the transaction. This is
the least restrictive of the four isolation levels.
It specifies that shared locks are placed on all data that
is used in a query. This prevents other users from updating the data and it
also prevents non-repeatable reads. In this scenario a transaction is able to
read the same row multiple times and gets a different value each time. But
another user can insert new phantom rows into the data set.
It places a range lock on the data set, preventing other
users from updating or inserting rows that could fall in that range into the
data set until the transaction is complete. This is the most restrictive of the
four isolation levels. Locking on a higher level than the row/record level -
the table level does this. This is the only isolation level that removes the
possibility of phantom rows.
It stores a copy of the data transaction accesses. As a
result, the transaction will not see the changes made by other transaction.
This approach reduces blocking. Because even if other transactions are holding
locks on the data, a transaction with snapshot isolation will still be able to
read a copy of the data. This option is supported only in SQL Server 2005 and
needs to be enabled through a database level option.