Understanding Snapshot Isolation

10/10/2010 6:31:08 PM
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.

Listing 1. Enabling snapshot isolation.

Next, create the TestTable table, for use in our example.

Listing 2. Creating and populating TestTable.

Open a tab in SSMS and start a transaction that inserts one more row into the table, and keep the transaction open.

Listing 3. Tab 1, an open transaction that inserts a row into TestTable.

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.

Listing 4. Tab 2, when not using snapshot isolation, a query is blocked by the outstanding transaction in Tab 1.

Cancel the query in Tab 2, and run it again under SNAPSHOT isolation level, as shown in Listing 5.

Listing 5. Tab 2, when using SNAPSHOT isolation, the same query completes.

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.

Listing 6. Tab 3, when using READ_COMMITTED_SNAPSHOT isolation, the query also completes right away.

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.

Top 10 Video Game
-   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Total War: Warhammer [PC] Demigryph Trailer
-   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
-   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
-   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
-   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
-   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
-   Satellite Reign [PC] Release Date Trailer