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.
5 Comments. Leave new
Hi Hugo,
Another way would be to create an indexed view as follows:
CREATE VIEW dbo.CustomerTypeAOrders WITH SCHEMABINDING
AS
SELECT c.CustID, o.OrderId
FROM dbo.Orders o JOIN dbo.Customers c ON c.CustID = o.CustID
WHERE o.OrderType = ‘A’
go
CREATE UNIQUE CLUSTERED INDEX UCI_CustomerTypeAOrders ON dbo.CustomerTypeAOrders(CustID, OrderId)
go
Modifications will serialize because they will acquire update locks on dbo.CustomerTypeAOrders. Makes sense?
Hi Alex,
Thanks for your thoughts.
I don’t see how this indexed view would prevent me from entering type ‘A’ orders for a non-existing customer. I’ve tried it, and I could execute
INSERT INTO Orders (OrderID, OrderType, CustID)
VALUES (‘O2’, ‘A’, 3)
just fine. Am I missing something?
Hi Hugo,
Sorry for my incomplete explanations. Here is a complete repro script:
CREATE TABLE Customers
(CustID int NOT NULL PRIMARY KEY,
CustName varchar(40) NOT NULL
);
CREATE TABLE Orders
(OrderID char(7) NOT NULL PRIMARY KEY,
OrderType char(1) NOT NULL CHECK (OrderType IN (‘A’, ‘B’)),
CustID int NOT NULL
);
INSERT INTO Customers (CustID, CustName)
VALUES (1, ‘First test customer’);
INSERT INTO Customers (CustID, CustName)
VALUES (2, ‘Second test customer’);
go
CREATE VIEW dbo.CustomerTypeAOrders WITH SCHEMABINDING
AS
SELECT c.CustID, o.OrderId
FROM dbo.Orders o JOIN dbo.Customers c ON c.CustID = o.CustID
WHERE o.OrderType = ‘A’
go
CREATE UNIQUE CLUSTERED INDEX UCI_CustomerTypeAOrders ON dbo.CustomerTypeAOrders(CustID, OrderId)
go
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
——————– in the first tab run this
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
— 1. Check to see that the customer has no orders
SELECT *
FROM Orders
WHERE CustID = 1;
— Remove the customer
DELETE Customers
WHERE CustID = 1;
— but do not commit yet
—- 2. in the second tab run this:
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);
— statement hangs
— 3. open up an Activity window and note that the command is suspended
— 4. go to first window and commit
— note that the second window displays an error right away:
(1 row(s) affected)
Msg 3960, Level 16, State 2, Line 15
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘dbo.Customers’ 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.
—- 5. Run SELECT @@SPID In both windows and make sure they match what you saw in Activity Monitor
— 6. restore the data:
INSERT INTO Customers (CustID, CustName)
VALUES (1, ‘First test customer’);
— 7. and drop the view
DROP VIEW dbo.CustomerTypeAOrders
— now repeat steps 1 and 2 – this time you can add an orphan all right
Hugo, has snapshot got any better in SQLServer 2008 ? Since I never used it on production (when I used, was with Firebird – which record versioning implementatio came from Interbase, which first version was on mid-80s), I never though MS has screwed so much….
Sorry for writing on so old topic…
Hi Fabricio,
As far as I know, there have been no changes to snapshot in SQL Server 2008.
However, I don’t consider snapshot isolation to be bad, as I don’t think there are better alternatives. The only way to preserve data integrity is to check modifications against existing (other) data. If you choose to use old, possibly stale, data for this check, you run the risk of ruining integrity. And if you choose to use current data, you have no choice but to wait until pending other changes to that data have been either committed or rolled back, in other words, until locks are released. Since integrity should be the first priority, I think Microsoft made the right choice. My only problem is that the phrase "readers don’t block writers" is too simplistic.
With custom-built integrity checks (triggers, CHECK constraints that call user-defined functions, etc), you get more control and more repsonsibility. You can override a snapshot setting by using SET TRANSACTION ISOLATION LEVEL or query hints to make sure that snapshot isolation, if in effect, will be bypassed for the check. Or you can choose to take the risk of checking against stale data. Your choice, and the consequences are yours as well.
Thanks for your comment!