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:
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.
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.
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.
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.
Next, add some test data to our tables, as shown in Listing 5.
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.
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.
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.
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.
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.
In the second tab, reopen the closed ticket, as shown in Listing 11.
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.
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.
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.
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.
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.
