Locking and Concurrency - SQL Server 2012 Internals and Troubleshooting - page 178

If SET TRANSACTION ISOLATION LEVEL READ COMMITTED; is substituted in the preceding example,
then both the shared locks from the repeatable read isolation level and the range locks of the serial-
izable level will be eliminated, leaving only the shared lock on the whole database. See Figure 6-20.
FIGURE 6-20
Read Uncommitted/NOLOCK
We mention the NOLOCK hint here because that is how many developers force the read uncommitted
isolation level on their system. It is the least pessimistic isolation level, but it still is not classed as
optimistic.
In the read uncommitted isolation level, shared (S) locks are not taken out at all. This also applies
if the transaction is using a different isolation level but the NOLOCK hint is used. The upshot of this
is the problem of dirty reads described earlier. Read transactions are not blocked by data with
exclusive locks, but the data they read is of a potentially dubious value. Performance is increased, as
without shared locks being acquired, there is no lock compatibility to be checked.
Note that some locks can still block reads in this isolation level — locks that stop anything getting
near data, such as schema modii cation locks. The behavior of this isolation level has already been
demonstrated as part of the dirty reads problem, so that code is not repeated here.
Snapshot
The optimistic snapshot isolation level is turned on using the command
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; . Before this can be done, however,
the database must be coni gured to allow it, as shown here:
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON;
After the snapshot isolation level is set, the database can perform the extra work required when a
transaction starts, ensuring that for the length of that transaction, the entire database appears as it
did at the start of it. This has an interesting effect on the locking required for reads — no locks are
required.
This may sound useful, but every time data is changed, the previous copy of the data must be stored
until every transaction that was active when the change was made has been completed (except its
own transaction, which naturally sees the newer copy of the data). The data to support this behavior
is kept in the tempdb database.
Read Committed Snapshot
This isolation level is similar to the snapshot isolation level, but it only provides statement-level
read consistency. Therefore, the behavior feels more like the read committed isolation level, with