Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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.


advertisement
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:

SELECT @@SPID GO BEGIN TRANSACTION UPDATE Tickets SET LotsOfComments = 'Reports running very slowly' WHERE TicketId = 2

In the second tab, start your report as follows:

SELECT @@SPID GO 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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap