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 4

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.

MSDN recommends using READ COMMITTED SNAPSHOT rather than SHAPSHOT under most circumstances. It might also be easier to use. Once enabled, it becomes your default isolation level. However, in some cases, READ COMMITTED SNAPSHOT is not your best choice. Because READ COMMITTED SNAPSHOT, once enabled in the database, becomes the default isolation level, you can end up with breaking changes in you applications. For example, suppose READ COMMITTED SNAPSHOT is not enabled in the database and you want to assign one more ticket to a person, but only if that user does not already have high priority tickets:

UPDATE Tickets SET AssignedTo = 6 WHERE TicketId = 1
AND NOT EXISTS(SELECT 1 FROM Tickets WHERE AssignedTo = 6 AND Priority='High')
--- do not commit yet

Note that you have not explicitly specified an isolation level, so your transaction runs under the default READ COMMITTED level. If another connection issues a similar update:

UPDATE Tickets SET AssignedTo = 6 WHERE TicketId = 2
AND NOT EXISTS(SELECT 1 FROM Tickets WHERE AssignedTo = 6 AND Priority='High')

it will hang in a lock waiting state. Once you commit your first transaction the second one will complete, but it will not assign ticket 2 to user 6, which is the correct behavior as designed.

Suppose your DBA has enabled READ COMMITTED SNAPSHOT. When you rerun your two queries, you will end up with two high-priority tickets assigned to the same person—not as designed, and you will not receive any warning messages whatsoever! (For more examples of breaking changes introduced by snapshot isolation, refer to Hugo Kornelis's series of articles on sqlblog.com and to my previous DevX article "Avoid These Common Business Rules Implementation Mistakes.")

As you have seen, if you rely on the default isolation level in your queries, you might prefer to use SNAPSHOT over READ COMMITTED SNAPSHOT, at least until you have explicitly specified READ COMMITTED whenever you assumed it. Also note that in some cases transactions behave differently under the SNAPSHOT isolation level than they do under the READ COMMITTED SNAPSHOT isolation level. In any case, explicitly specifying transaction isolation levels prevents your code from breaking after database configuration changes.

Alexander Kuznetsov has over 10 years of experience in database design, development, troubleshooting, and administration. Currently, he works with DRW Trading Group and concentrates on database design, development, and performance improvements.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date