Defensive Database Programming with SQL Server : When Snapshot Isolation Breaks Code

8/13/2011 4:08:36 PM
Queries that may previously have been blocked, in lock-waiting state, under traditional isolation levels, can complete when running under the snapshot isolation levels. This feature is highly useful in many situations but, unfortunately, it may in some cases break existing code, such as triggers.

The following example demonstrates a typical scenario where triggers are used to enforce a business rule that involves rows in different tables. The triggers, previously working normally, begin to intermittently fail when they run under snapshot isolation. Note that, although I have chosen triggers to demonstrate the problem, I could just as easily provide similar examples using stored procedures. In other words, triggers are no more or less vulnerable to this issue than stored procedures.

Suppose that we need to implement two business rules, expressed as follows:

  • developers cannot go on vacation if they have active tickets assigned to them

  • inactive tickets cannot be changed to active status if they are assigned to developers who are on vacation.

We are going to use triggers to implement these business rules. As noted, this is not the only implementation option, but it is a very common approach. Listing 1 creates the Developers and Tickets tables that form the basis of our implementation. Let me repeat that these examples should be not be run in the tempdb database.

Listing 1. Creating the Developers and Tickets tables.

Note that, thanks to our FOREIGN KEY constraint, FK_Tickets_Developers, newly added developers cannot have tickets assigned to them. We only need to make sure our rules are enforced at the point when a developer's status is updated. Therefore, on the Developers table, we create an AFTER UPDATE trigger, as shown in Listing 2. It is designed to make sure that developers cannot go on vacation if they have active tickets assigned to them.

Listing 2. The Developers_Upd trigger.

Similarly, on the Tickets table, we create an AFTER UPDATE trigger, as shown in Listing 3. It is designed to make sure that inactive tickets cannot be changed to active status if they are assigned to developers who are on vacation. In a complete solution, we would also need to create an AFTER INSERT trigger to ensure that it is impossible to insert a new ticket with active status and assign it to a developer who is on vacation. However, we will focus only on the AFTER UPDATE trigger here.

Listing 3. The Tickets_Upd trigger.

Trigger behavior in normal READ COMMITTED mode

Let's test these triggers out under the default READ COMMITTED isolation level. First of all, we need to make sure that we are not running under READ_COMMITTED_SNAPSHOT isolation level, as shown in Listing 4.

Listing 4. Make sure that READ_COMMITTED_SNAPSHOT is turned off.

Next, add some test data to our tables, as shown in Listing 5.

Listing 5. Adding test data to the Developers and Tickets tables.

Our test data includes one developer (Arnie) who is currently active and has one active ticket assigned to him, so we can test what happens if Arnie attempts to go on holiday without assigning his ticket to someone else. Apparently, under READ COMMITTED isolation level, our Developers_Upd trigger prevents violation of this business rule, as shown in Listing 6.

Listing 6. Testing the Developers_Upd trigger.

Similarly, our dbo.Tickets_Upd trigger ensures that inactive tickets cannot be changed to active status if they are assigned to developers who are on vacation, as shown in Listing 7.

Listing 7. Testing out the Tickets_Upd trigger.

So, it looks like our triggers work and our two business rules are enforced. However, so far we have only tested them from a single connection. What happens when we have concurrent access, so our triggers are being fired from multiple connections?

To mimic real life concurrency, we'll begin two transactions in two tabs, but not commit them. That will ensure that two modifications are active at the same time. In one tab, run the script shown in Listing 8, which first resets the test data to ensure Arnie's ticket is closed, and then starts the test transaction, which will send him on vacation.

Listing 8. Arnie is on vacation, but the change has not committed yet.

Note that, when this script has finished, the transaction opened by this connection is still outstanding, because we have neither committed it nor rolled it back. In a second tab, try to reopen the ticket assigned to Arnie, as shown in Listing 9.

Listing 9. Attempting to reopen a ticket assigned to Arnie.

This script will not complete; it will stay in lock-waiting state, because the Tickets_Upd trigger needs to read the Developers table and find out whether Arnie is active or on vacation, and that row has been changed and is locked by the transaction in the first tab .

Go back to the first tab, and commit the outstanding transaction. The script in the second tab will immediately fail with the same error message as shown in Listing 4-13, which is the expected behavior.

Trigger behavior in SNAPSHOT mode

In fact, our triggers work in all the versions of SQL Server prior to SQL Server 2005. However, in SQL Server 2005, our triggers may fail when working in either READ_COMMITTED_SNAPSHOT or SNAPSHOT isolation mode.

This is very easy to demonstrate, simply by rerunning the previous example in SNAPSHOT isolation mode. If you haven't done so already, then you'll first need to enable SNAPSHOT isolation by running the ALTER DATABASE Test SET ALLOW_SNAPSHOT_ISOLATION ON command from Listing 1 of this article.

In one tab, run the script shown in Listing 10, which first resets the test data so that all tickets are closed, one developer (Arnie) is currently active, and the other (Carol) is on vacation. It then starts (but does not commit) a transaction that sets Arnie's status to Vacation.

Listing 10. The code in Tab 1 begins a transaction to set Arnie's status to Vacation.

In the second tab, reopen the closed ticket, as shown in Listing 11.

Listing 11. The code in Tab 2 starts a transaction to reopen a ticket that is assigned to Arnie.

This time, under SNAPSHOT isolation level, this script also completes. The reason is simple: under SNAPSHOT isolation writers do not block readers; when the SELECT statement in the Tickets_Upd trigger executes, SQL Server detects that the required row is subject to pending changes and so retrieves from the "version store" the row as it existed at the time the transaction started.

In other words, from the perspective of Tickets_Upd trigger Arnie's status is still Active (I shall prove this shortly).

To complete the example, commit the transactions in both tabs and, as you can see in Listing 12, our data now violates our business rules.

Listing 12. Data integrity is violated; we have an active ticket assigned to the developer who is on vacation.

To prove that the Tickets_Upd trigger does not see the uncommitted changes from another transaction, we can embed a query in the trigger's body that will show exactly what data our trigger sees. Of course, this embedded query is for troubleshooting only and should never be included in production code.

Listing 13. Modifying the Tickets_Upd trigger so that it reports the data, as it sees it, in the Developers table.

Now, simply run through the example again, running Listing 10 in one tab, resetting the test data and then starting a transaction to set Arnie's status to "vacation," and Listing 14 in a second tab.

Listing 14. The Tickets_Upd trigger does not see the uncommitted changes from the other transaction.

Clearly the Tickets_Upd trigger is not blocked by the modification in the first tab; it just reads the corresponding row in the Developers table as it existed at the time the transaction began.

Before moving on, remember to commit (or roll back) both of the open transactions.

Building more robust triggers?

Our example has demonstrated that triggers that work perfectly well under traditional isolation levels can fail when using the new snapshot isolation level. Could we have developed more robust triggers; ones that continue to enforce our business rules under snapshot isolation? In this case, I do not think so. We might have been completely unaware of snapshot isolation at the time we were developing our triggers.

However, at the point that we plan to implement a new feature, such as snapshot isolation, we need to be fully aware of all the downstream ramifications of using it, and look for ways to either ensure our triggers function correctly, or find an alternative way to enforce our business rules. In this case, the fix takes the form of a very simple modification to our Tickets_Upd trigger, to add the READCOMMITTEDLOCK hint, as shown in Listing 15. Note that we also remove our previous "troubleshooting" query.

Listing 15. Adding the READCOMMITTEDLOCK hint to the Tickets_Upd trigger.

The READCOMMITTEDLOCK hint in the body of our trigger ensures that the query to which it applies, against the Developers table, runs under the READ COMMITTED isolation level. If we rerun our tests, the Tickets_Upd trigger behaves under SNAPSHOT isolation level exactly as it does under READ COMMITTED isolation level.

Advanced Use of Constraints, is a rather advanced, non-trigger-based solution to this type of problem. It uses constraints and, at the time of writing, works consistently under all isolation levels.

Before moving on, commit or roll back any outstanding transactions and then re-establish READ COMMITTED as our default isolation level, by rerunning Listing 4.

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone