The default transaction isolation level in SQL Server is READ COMMITTED,
which prevents statements from reading data that has been modified by
another transaction, but not committed. Most applications are developed
to run in this mode. However, in order to enforce this mode, SQL Server
has to use shared and exclusive locks in order to prevent data being
read that is currently being modified by another connection, and to
prevent other transactions modifying data that is currently being read.
If a statement running under READ COMMITTED
isolation level encounters data that is being modified by another
transaction, it must wait until those changes are either committed or
rolled back before proceeding.
SQL Server 2005
introduced the new snapshot isolation levels, with the goal of
"enhancing concurrency for OLTP applications." It is well beyond the
scope of this book to offer a full explanation of how the snapshot
isolation levels work, and I refer you to Books Online for that.
However, the essential difference is that under snapshot isolation, SQL
Server maintains in tempdb a
time-stamped "version store" of all data changes since the beginning of
the oldest outstanding transaction. Instead of blocking when it
encounters an exclusive lock, a reader transaction will simply "read
around" the lock, retrieving from the version store the version of the
row consistent with a certain point in time. snapshot isolation
introduces two new modes of operation:
SNAPSHOT mode – queries running in this mode return committed data as of the beginning of the transaction
READ_COMMITTED_SNAPSHOT mode – queries return committed data as of the beginning of the current statement.
To demonstrate how snapshot isolation works, it first has to be enabled, as shown in Listing 1. Note that it is enabled at the database level and, because we cannot use either of the types of snapshot isolation in tempdb, be sure to choose a database other than tempdb for these examples.
Next, create the TestTable table, for use in our example.
Open a tab in SSMS and start a transaction that inserts one more row into the table, and keep the transaction open.
Without snapshot isolation, a
query may be blocked if some of the data it needs to read is locked. To
demonstrate this, open a second tab and run the code in Listing 4.
Cancel the query in Tab 2, and run it again under SNAPSHOT isolation level, as shown in Listing 5.
The same query under READ_COMMITTED_SNAPSHOT also completes, and the output is exactly the same as in the previous listing. Open a new tab and run the script shown in Listing 6.
So far, the queries return exactly the same results under either SNAPSHOT or READ_COMMITTED_SNAPSHOT mode. To observe the difference between the two modes, return to Tab 1 (Listing 3) and commit the modification. Go to Tab 2 (Listing 5), which is running in SNAPSHOT
mode, and rerun the query. Since we started the initial transaction
before the addition of the second row was committed, only the row
committed before the transaction began is returned. Now go to Tab 3 (Listing 6), which is running in SNAPSHOT_READ_COMMITTED mode, and rerun the query. Since the second row was committed before the statement was run, both rows will be returned.
As a final clean up, highlight and execute COMMIT in the tabs for Listings 5 and 6.
