Snapshot isolation: A threat for integrity? (Part 2)

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.

Snapshot isolation: A threat for integrity? (Part 1)
Snapshot isolation: A threat for integrity? (Part 3)

Related Posts

No results found

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.

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu
%d bloggers like this:

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close