In the previous parts of this series, I have shown how SQL Server prevents violations of foreign key constraints by silently disabling snapshot isolation. I have also demonstrated how you can use the same technique inside your trigger code, to keep snapshot isolation from damaging your custom integrity rules. Now, in the final part of this series, I will investigate some less common techniques for preserving integrity. Note that I would normally not recommend any of these techniques, but I do see them often enough in newsgroup postings. Apparently, they are used.
First, I’ll set up the tables again. I’ll just continue to use the script from part 2, so no need to repeat it here. I’ll also use the same business rule: orders of type A must refer to an existing customer. Instead of implementing this rule with a trigger, I use a trick I often see recommended in the newsgroups: use a CHECK constraint, based on a user-defined function.
CREATE FUNCTION dbo.CustExists (@CustID int)
RETURNS char(1)
AS
BEGIN
DECLARE @retval char(1)
IF EXISTS (SELECT *
FROM dbo.Customers
WHERE CustID = @CustID)
SET @retval = 'Y'
ELSE
SET @retval = 'N'
RETURN @retval
END;
go
ALTER TABLE dbo.Orders
ADD CONSTRAINT TypeAMustExist
CHECK (OrderType <> 'A' OR
dbo.CustExists(CustID) = 'Y');
go
Note that this constraint offers only partial protection: nothing prevents you from deleting rows from the Customers table, even if they are referenced by type A orders – you will have to take additional steps to prevent that. Only insertions and updates in the Orders table are checked with this constraint – but with snapshot isolation, not even that is reliable anymore.
To test this, I opened two connections from SQL Server Management Studio. In the first, I entered and executed this code:
USE SnapshotTest;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
-- Check to see that the customer has no orders
SELECT *
FROM Orders
WHERE CustID = 1;
-- Remove the customer
DELETE Customers
WHERE CustID = 1;
-- Twiddle thumbs for 10 seconds before commiting
WAITFOR DELAY '0:00:10';
COMMIT TRANSACTION;
go
-- Check results
SELECT * FROM Customers;
SELECT * FROM Orders;
go
In the second window, I entered this code, and I ensured that I started this some 5 seconds after starting the query in the first window.
USE SnapshotTest;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
-- Check to see that the customer exists
SELECT *
FROM Customers
WHERE CustID = 1;
-- Insert an order for the customer
INSERT INTO Orders (OrderID, OrderType, CustID)
VALUES ('Order01', 'A', 1);
-- No need to wait here. COMMIT right now.
COMMIT TRANSACTION;
go
-- Check results
SELECT * FROM Customers;
SELECT * FROM Orders;
go
The second transaction finished directly, indicating that the reader (the user-defined function) was not blocked by the writer (the other connection). The results indicated that the order was inserted just fine, and that customer 1 still existed (after all, the DELETE from the other transaction was not yet committed and snapshot isolation hides those uncommitted changes from me). However, five seconds later the other transaction was finished as well, and now I did have a type A order with a non existing customer!
To fix this, I should probably try to have the function disable snapshot isolation, just as I did in the trigger in the previous instalment. Here’s the changed code:
CREATE FUNCTION dbo.CustExists (@CustID int)
RETURNS char(1)
AS
BEGIN
IF (SELECT transaction_isolation_level
FROM sys.dm_exec_session
WHERE session_id = @@SPID) = 5
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @retval char(1)
IF EXISTS (SELECT *
FROM dbo.Customers
WHERE CustID = @CustID)
SET @retval = 'Y'
ELSE
SET @retval = 'N'
RETURN @retval
END;
And here’s the output. Ouch!
Msg 443, Level 16, State 15, Procedure CustExists, Line 8
Invalid use of side-effecting or time-dependent operator in 'SET TRANSACTION ISOLATION LEVEL' within a function.
Does that means I’m busted? No, not quite. Instead of using SET to force a different isolation level, I can also use table hints. The catch is that I can’t make the chosen isolation level dependant on the existing level, so if the function is called from a transaction that uses SERIALIZABLE isolation, it will also be overridden. Also note that the hint must be repeated for each table used. In this example, it’s only needed once:
CREATE FUNCTION dbo.CustExists (@CustID int)
RETURNS char(1)
AS
BEGIN
DECLARE @retval char(1)
IF EXISTS (SELECT *
FROM dbo.Customers WITH (READCOMMITTEDLOCK)
WHERE CustID = @CustID)
SET @retval = 'Y'
ELSE
SET @retval = 'N'
RETURN @retval
END;
With this version of the function, the snapshot isolation level will again be effectively negated. Repeating the tests above, I now see that the second transaction has to wait for the first to commit its changes, and after that it throws a constraint violation error.
Another quite (ahem!) “interesting” method of maintaining integrity is the use of a view WITH CHECK OPTION. The idea is to filter “illegal” data out of the view, remove modification access to the table and give modification access to the view instead. The WITH CHECK OPTION makes SQL Server throw an error when a row is inserted that would not be included in the view, or when a row is modified such that it would fall out of the view. This is a pretty creative way to enforce constraints; I’d never have thought of it until I saw this as a suggestion in a newsgroup posting by Alexander Kuznetsov (thanks, Alexander!). Here’s how I used this technique to enforce the “Type A must be existing customer” constraint in my example. Note that this technique, like the user-defined function, only works to prevent violations when inserting into or updating the orders table – you can still delete all customers and get no complaints from SQL Server!
CREATE VIEW LegalOrders
AS
SELECT OrderID, OrderType, CustID
FROM dbo.Orders AS o
WHERE OrderType <> 'A'
OR EXISTS (SELECT *
FROM dbo.Customers AS c
WHERE c.CustID = o.CustID)
WITH CHECK OPTION;
After defining this view, I can still violate the business constraint when inserting into the base table Orders, but not when inserting into the view LegalOrders. Just as we wanted. And, not entirely unexpected, inserting into LegalOrders even works if I refer to a customer that has just been removed in a different, uncommitted transaction, thanks to the wonders of snapshot isolation.
Since a view can only consist of a single SELECT statement, I won’t even try to use SET to change the transaction isolation level. But I will try what happens if I add locking hints:
CREATE VIEW LegalOrders
AS
SELECT OrderID, OrderType, CustID
FROM dbo.Orders AS o WITH (READCOMMITTEDLOCK)
WHERE OrderType <> 'A'
OR EXISTS (SELECT *
FROM dbo.Customers AS c WITH (READCOMMITTEDLOCK)
WHERE c.CustID = o.CustID)
WITH CHECK OPTION;
And sure enough, we again lose the concurrency advantage of snapshot isolation, but data integrity is preserved.
And that brings me to the end of this four-part series on snapshot isolation. The most important conclusions, for me, are:
· SQL Server will automatically temporarily disable snapshot isolation when checking FOREIGN KEY constraints. This is basically a good thing, since it ensures the integrity of my data, but it does severely limit the concurrency benefit that snapshot isolation is supposed to deliver.
· If you use triggers, user-defined functions or any other technique to check integrity of your data, then you must be aware of the potential damage that snapshot isolation can do to your database. You should use either SET TRANSACTION ISOLATION LEVEL or locking hints to force at least read committed isolation. Even though this will reduce your database’s concurrency, it will at least ensure that integrity is maintained.