THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

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.

Published Wednesday, July 26, 2006 6:12 PM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' said:

Though it took longer than I expected, part three of the series is now finally up. Read it if you want to find out how to modify your triggers so that they are no longer thrashed by snapshot isolation.

August 25, 2006 11:18 AM
 

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' said:

The fourth and final installment of this series is now available

September 15, 2006 1:59 PM
 

Alexander Kuznetsov said:

There are three kinds of triggers: those which blow up and those which fail silently ;). Seriously, there

May 11, 2009 9:19 PM
 

Steve Haslam said:

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.

December 14, 2010 11:20 AM

Leave a Comment

(required) 
(required) 
Submit

About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement