In the first part of this series, I showed how SQL Server 2005 prevents violation of foreign key constraints when using snapshot isolation by automatically and silently using a less concurrent isolation level. In this part, I’ll show how snapshot isolation can be used to really mess up your data.
I’ll use the same sample tables I did in the first part, with one difference: only type A orders have to be for an existing customer; type B orders still need to have a customer ID included, but it doesn’t have to refer to an existing row in the Customers table. This business rule can’t be enforced by a foreign key constraint (at least not without changing the schema to include a computed column), so we’ll have to use something else. But first, let’s create the test database, the tables, and some test customers.
USE master;
go
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'SnapshotTest')
BEGIN;
ALTER DATABASE SnapshotTest
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
DROP DATABASE SnapshotTest;
END;
go
CREATE DATABASE SnapshotTest;
go
ALTER DATABASE SnapshotTest
SET ALLOW_SNAPSHOT_ISOLATION ON;
go
USE SnapshotTest;
go
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
One way to enforce this constraint is through triggers. New and updated type A orders should be checked to see if they refer to an existing customer, and deleted customers have to be checked for any type A orders referring to them. Actually, updated customers should be checked as well since SQL Server allows the change of columns included in the primary key, but for brevity sake, I’ll leave that out for this example. I’ll also use the bare minimum of error handling – don’t consider the triggers below as good examples for error handling in real-life applications!
USE SnapshotTest;
go
CREATE TRIGGER Orders_iu
ON Orders
AFTER INSERT, UPDATE
AS
IF EXISTS
(SELECT *
FROM inserted AS i
WHERE i.OrderType = 'A'
AND NOT EXISTS
(SELECT *
FROM Customers AS c
WHERE c.CustID = i.CustID))
BEGIN;
RAISERROR ('Type A orders must refer to existing customers', 16, 1);
ROLLBACK TRANSACTION;
END;
go
CREATE TRIGGER Customers_d
ON Customers
AFTER DELETE
AS
IF EXISTS
(SELECT *
FROM deleted AS d
INNER JOIN Orders AS o
ON o.CustID = d.CustID
WHERE o.OrderType = 'A')
BEGIN;
RAISERROR ('Customers with type A orders can''t be deleted', 16, 1);
ROLLBACK TRANSACTION;
END;
go
With those triggers installed, it’s time to check if enabling snapshot isolation manages to mess up our integrity. I’ll use the same tests I did in the first part. Here’s the SQL for connection 1.
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);
-- Twiddle thumbs for 10 seconds before commiting
WAITFOR DELAY '0:00:10';
COMMIT TRANSACTION;
go
-- Check results
SELECT * FROM Customers;
SELECT * FROM Orders;
go
And here’s the SQL for connection 2.
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
I start executing the SQL in both connections. They don’t block each other (as was to be expected since we’re using snapshot isolation) – and the end result is a violation of our business rules. Regardless of which connection starts first, the end result after executing both connections is always this:
CustID CustName
----------- ----------------------------------------
2 Second test customer
OrderID OrderType CustID
------- --------- -----------
Order01 A 1
Indeed – we now have a type A order referring to a non-existing customer, exactly the scenario that we wanted to avoid.
For now, the bottom line is that snapshot isolation and triggers don’t match. But don’t tear down and re-build your code yet – there is a workaround. I’ll get to that in the next part of this series.