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 3

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 2. How Snapshot Isolation Prevents Lost Updates
In this scenario, a long-running transaction can detect lost updates under SNAPSHOT but not under READ COMMITTED SNAPSHOT. Also, in this scenario a query running under snapshot isolation exhibits intrinsic optimistic locking behavior. Consider a long batch job that archives old tickets into a history table called TicketsArchive with the same structure as the Tickets table. Assume that archiving is implemented as one large transaction that users need to be able to abort. The implementation of aborting is simple: if a row is inserted into the CancelArchiving table while the archiving transaction is running, the archiving transaction is supposed to detect it and abort.

To start the batch, begin by inserting old tickets into a history table (TicketsArchive) as follows:

DELETE FROM CancelArchiving
INSERT INTO TicketsArchive(TicketId, AssignedTo, Priority, Status,
  SELECT TicketId, AssignedTo, Priority, Status, LotsOfComments
  FROM Tickets WHERE TicketId < 10

The next step involves archiving various attachments, and it takes considerable time. Suppose that while the process is running, a different connection inserts a request to cancel out:

INSERT INTO CancelArchiving(ReasonForCancel) VALUES('Monthly Report For CEO Not Started Yet')

Other connections continue to modify the data in the range that is going to be deleted, as follows:

UPDATE Tickets SET AssignedTo = 4 WHERE TicketId = 1
INSERT INTO Tickets(TicketId, AssignedTo, Priority, Status, LotsOfComments) 
VALUES(5, 5, 'Medium', 'Opened', 'Aggregates In Reports Do Not Match')

Eventually, the archiving transaction gets to the point when it tries to check if there is a request to cancel it:

DECLARE @IsCancelled INT
SET @IsCancelled = (SELECT COUNT(*) FROM CancelArchiving)
PRINT @IsCancelled 

Under READ COMMITTED SNAPSHOT, the request to cancel is detected, because it was committed before this select started. Under SNAPSHOT, however, the request goes unnoticed, as are any other changes committed after the transaction has started—and this can be a breaking change! This is an important difference that you need to consider when choosing which snapshot isolation level to use. Suppose, however, that the transaction has no requests to cancel and proceeds to delete:

DELETE FROM Tickets WHERE TicketId < 10

Again, the results under READ COMMITTED SNAPSHOT and those under SNAPSHOT are very different. Under READ COMMITTED SNAPSHOT, the delete succeeds because the changes (the insert and the update in the range that is going to be deleted) were committed before this delete—this particular statement—started. As a result, the updates to tickets 1 and 5 disappear without any warning. This is yet another manifestation of a well-known problem of lost updates. On the other hand, under snapshot isolation, the problem is detected and the delete errors out:

Msg 3960, Level 16, State 2, Line 1
Snapshot isolation transaction aborted due to update conflict. You cannot use
snapshot isolation to access table 'dbo.Tickets' directly or indirectly in
database 'Sandbox' to update, delete, or insert the row that has been modified
or deleted by another transaction. Retry the transaction or change the isolation
level for the update/delete statement.

As you have seen, snapshot isolation conveniently provides optimistic locking for you. This capability may come in very handy in some situations. However, this feature may also cause breaking changes. Besides, in this particular case, it might be better to use the SERIALIZEABLE isolation level for large transactions. Anyway, snapshot isolation exposed a serious problem with lost updates. It is entirely up to you to decide how to fix it. (Note: the debate about pessimistic vs. optimistic locking is beyond the scope of this article. If you choose to use pessimistic locking, you can use higher isolation levels.)

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