RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


When Snapshot Isolation Helps and When It Hurts : Page 2

The benefits of SQL Server 2005's new snapshot isolation feature are well known, but SQL Server developers also need to understand the downstream ramifications of snapshot isolation.

Scenario 1. Reports in OLTP Environment Have Inconsistencies
Clearly the database structure guarantees a ticket to be assigned to one and only one user at a time. However, sometimes a simple query like this:

SELECT FirstName, LastName, TicketId, Priority, Status,
SUBSTRING(Tickets.LotsOfComments, 1, 25) Comments 
  FROM Users LEFT OUTER JOIN Tickets ON Users.UserId = Tickets.AssignedTo

returns obviously incorrect results:

FirstName LastName TicketId    Priority   Status     Comments
--------- -------- ----------- ---------- ---------- -------------------------
Jill      Larson   1           High       Opened     Cannot login             
Brian     Taylor   NULL        NULL       NULL       NULL
Lester    Rash     NULL        NULL       NULL       NULL
Sue       Miller   NULL        NULL       NULL       NULL
Justin    Rowler   NULL        NULL       NULL       NULL
Joe       Hanson   NULL        NULL       NULL       NULL
Brenda    Tyler    NULL        NULL       NULL       NULL
Drew      Wellner  2           High       Opened     Reports running very slow
Sue       Hogan    NULL        NULL       NULL       NULL
Xavier    Sanchez  1           High       Opened     Cannot login             
Xavier    Sanchez  3           High       Opened     Cannot save changes      

Why is ticket number 1 assigned to both Jill Larson and Xavier Sanchez? This intermittent problem manifests itself only when the data is selected and modified simultaneously. If you query only static data, you can never reproduce this problem. The following instructions show you how selecting data that is being modified can cause this elusive error. (Note that so far all queries run under the default isolation level, READ COMMITTED.)

In SQL Server Management Studio, open three tabs—that means three connections. Use the database in which you created sample tables. The query in each tab starts with SELECT @@SPID, so that you can identify these connections in the Activity Monitor. In the first tab, modify the Tickets table as follows:

UPDATE Tickets SET LotsOfComments = 'Reports running very slowly' WHERE TicketId = 2

In the second tab, start your report as follows:

SELECT FirstName, LastName, TicketId, Priority, Status, SUBSTRING(Tickets.LotsOfComments, 1, 25) Comments 
  FROM Users LEFT OUTER JOIN Tickets ON Users.UserId = Tickets.AssignedTo

Note that your report hangs. If you open Activity Monitor, you'll see that the report is blocked by the uncommitted update in the first tab. While the report is still waiting, make another modification against the Tickets table in the third tab as follows:

UPDATE Tickets SET AssignedTo = 10 WHERE TicketId = 1

Note that this update completes right away. Go to the first tab and commit. Note that the report in the second tab completes right away, and the results are incorrect (precisely the ones I listed at the beginning of this section). To make sure the report returns correct results, you can use a higher isolation level for it. That will increase lock contention.

For instance, if you repeat the same scenario but run the report under the REPEATABLE READ isolation level, the update in the third tab will not complete right away. It will be blocked by the report. You can open Activity Monitor and see that for yourself. Although the report will return the correct results, the price tag for consistency prior to SQL Server 2005 may be quite steep—increased lock contention. If you are already using SQL Server 2005, you can utilize one of the two new isolation levels, either SNAPSHOT or READ COMMITTED SNAPSHOT. (If you need detailed information for configuring your database to enable snapshot isolation, refer to "Choosing Row Versioning-based Isolation Levels" in MSDN.)

No matter which snapshot isolation level you choose, the report will return right away. Note that the report will show the old value for the description of ticket number 2. Instead of "Reports running very slow," you will see "Reports hanging." The uncommitted changes from the first tab are invisible to the report. In this particular case, the problem with a report was very easy to notice. In many similar cases, you may get slightly incorrect totals that go unnoticed for quite a while.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date