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.
1 Comment. Leave new
Interesting read… was comparing this with PostgreSQL, which does MVCC by default, to see if it handled things differently. By default, it has the same problem, until you put a "for share" clause into the "not exists" check in orders_iu- it creates a shared row lock on the referenced row, ensuring it can’t be changed or deleted until the transaction referencing that row has completed.
Presumably MSSQL’s fkey enforcement does the same thing as Postgresql’s- gets a shared row lock on referenced rows, which explains the behaviour in your first installment. If you can make the "not exists()" check take a shared row lock on the customer row in your trigger, you should be able to solve the problem that way too, without the fiddling with isolation levels you employ later on.