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

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

Hugo Kornelis — Fri, 15 Sep 2006 17:58:00 GMT

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)

BEGIN

DECLARE @retval char(1)

IF EXISTS (SELECT *

FROM dbo.Customers

WHERE CustID = @CustID)

SET @retval = 'Y'

ELSE

SET @retval = 'N'

RETURN @retval

END;

ALTER TABLE dbo.Orders

ADD CONSTRAINT TypeAMustExist

CHECK (OrderType <> 'A' OR

dbo.CustExists(CustID) = 'Y');

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;

-- Check results

SELECT * FROM Customers;

SELECT * FROM Orders;

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;

-- Check results

SELECT * FROM Customers;

SELECT * FROM Orders;

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)

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)

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

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

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.

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

Hugo Kornelis — Fri, 25 Aug 2006 15:17:00 GMT

Last month, I showed how snapshot integrity can really mess up your triggers, promised you a workaround, and then went on holiday. Some events in my life kept me from posting the promised workaround for longer than intended, so I hope that you haven’t been holding your breath for it! J

The workaround is actually quite simple. Just remember the first part of this series, where I showed how SQL Server 2005 prevents violation of foreign key constraints when using snapshot isolation – it automatically and silently switches to a less concurrent isolation level. Of course, if Microsoft can do it, then so can I!

I’ll use the same sample I did in the previous part, so there’s no need to repeat them here. You can copy the DDL from my last post. To mimic what Microsoft does when checking FOREIGN KEY constraints (i.e. automatically disable snapshot integrity) I added the command below as the very first command in both triggers:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

This change is all I need to fix last month’s bad data. I don’t even have to reset the isolation level to snapshot at the end of the trigger code: since changing the isolation level in a trigger or stored procedure never affects the isolation level of the calling procedure, resetting the isolation level at the end of the trigger would do nothing but waste a few CPU cycles.

Now, when I rerun my tests, the second connection waits for the first one to finish, then aborts with an error message. Just as when I tested the FOREIGN KEY constraint, except that the error message is now different from the awfully non-descriptive error message from my first tests:

Msg 50000, Level 16, State 1, Procedure Customers_d, Line 13

Customers with type A orders can't be deleted

Msg 3609, Level 16, State 1, Line 9

The transaction ended in the trigger. The batch has been aborted.

That’s it. Just one simple modification to fix the problems, and no catch, right? No, of course not – there is a catch. There always is. You see, these triggers will also execute if customers are deleted and if orders are inserted or updated from a procedure that uses a higher isolation level. If I set my isolation level to repeatable read, I expect the code in the trigger to honour that isolation level, not to disregard it!

To ensure that the isolation level is only changed to read committed if it was snapshot isolation, I’ll have to enclose the SET command in an IF statement that tests the current isolation level. Unfortunately, that’s not as straightforward as it sounds. The current isolation level is available from DBCC USEROPTIONS, which doesn’t return a parameter or return code, but produces a table. I’ll have to catch that output into a temporary table, then find the row with the isolation level and use that in my IF statement. Here is the code that has to go at the beginning of booth triggers, instead of the single line above:

CREATE TABLE #Options

(OptName varchar(128) PRIMARY KEY,

Value varchar(50));

INSERT INTO #Options (OptName, Value)

EXEC ('DBCC USEROPTIONS WITH NO_INFOMSGS;');

IF (SELECT Value

FROM #Options

WHERE OptName = 'isolation level') = 'snapshot'

BEGIN;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

END;

Repeating my tests shows that this code still escapes from snapshot isolation to read committed isolation to ensure that my data integrity can not be violated. But I now also know that the isolation level will not be changed if the trigger was invoked with any isolation level other than snapshot isolation.

The downside of this workaround is that improved data integrity goes hand in hand with reduced concurrency. The whole reason why snapshot isolation has been added to the product is to ensure that people who have to read data and people who have to change data won’t block each other. But since Microsoft will automatically disable snapshot isolation when checking foreign key constraints, and I disable snapshot isolation in my triggers, then there are not too many situations left for readers and writers to co-exist without blocking.

In the last part of this series, I’ll look at some other, less obvious methods that can be used to guard data integrity, and check how they are affected by snapshot integrity.

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

Hugo Kornelis — Wed, 26 Jul 2006 15:12:00 GMT

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;

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'SnapshotTest')

BEGIN;

ALTER DATABASE SnapshotTest

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

DROP DATABASE SnapshotTest;

END;

CREATE DATABASE SnapshotTest;

ALTER DATABASE SnapshotTest

SET ALLOW_SNAPSHOT_ISOLATION ON;

USE SnapshotTest;

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');

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;

CREATE TRIGGER Orders_iu

ON Orders

AFTER INSERT, UPDATE

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;

CREATE TRIGGER Customers_d

ON Customers

AFTER DELETE

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;

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;

-- Check results

SELECT * FROM Customers;

SELECT * FROM Orders;

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;

-- Check results

SELECT * FROM Customers;

SELECT * FROM Orders;

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)

Hugo Kornelis — Fri, 21 Jul 2006 17:40:00 GMT

One of the great new features of SQL Server 2005 is the snapshot isolation level. But exactly how safe is that feature? Can you still guarantee your data integrity if you use snapshot isolation level?

With most forms of data integrity, this is not an issue. But with referential integrity, it might be - after all, checking referential integrity usually requires the database to read data in other tables than the one being updated. Since readers and writers are supposed not to block each other if you use snapshot isolation, it’s easy to imagine a scenario where two concurrent data modifications try to make changes that collide with each other, yet both are allowed because of the snapshot isolation level.

Since this is quite a broad subject, I’ll write at least two blog entries about it. In this first instalment, I’ll look into how SQL Server handles this potential thread to integrity for the most common form of referential integrity: the foreign key constraint. Since testing is the only way to find out, I used the following code to create a test database with two tables and one foreign key constraint.

USE master;

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'SnapshotTest')

BEGIN;

DROP DATABASE SnapshotTest;

END;

CREATE DATABASE SnapshotTest;

ALTER DATABASE SnapshotTest

SET ALLOW_SNAPSHOT_ISOLATION ON;

USE SnapshotTest;

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) CHECK (OrderType IN ('A', 'B')),

CustID int NOT NULL REFERENCES Customers (CustID)

);

INSERT INTO Customers (CustID, CustName)

VALUES (1, 'First test customer');

INSERT INTO Customers (CustID, CustName)

VALUES (2, 'Second test customer');

With these two tables all set up, it’s time to start some testing. Let’s see if we are able to add an order for our only customer in one connection and at the same time remove that customer in another connection. 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;

-- Check results

SELECT * FROM Customers;

SELECT * FROM Orders;

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;

-- Check results

SELECT * FROM Customers;

SELECT * FROM Orders;

If I first start the SQL for connection 1, then (using less than 10 seconds) switch to connection 2 and start that SQL, the results are both encouraging and disencouraging at the same time. The SELECT statement at the beginning of the transaction is not blocked by the INSERT from the other transaction and shows the stale data, as expected in the snapshot isolation level. However, the DELETE statement (that, under the covers, uses the exact same read operation to check the foreign key constraint), is blocked.

The good news is that this means that even with snapshot isolation, it is impossible to violate a foreign key constraint. Considering the importance and value of the data that is sitting in our databases and the enormous costs involved with cleaning up bad data in databases that fail to guard integrity, this is Very Good News indeed.

But there’s bad news as well. Keeping our data integrity safe does come at a price. It means that the entire commercial blurb about how snapshot isolation improves concurrency because readers and writers no longer block each other has to be taken with a grain of salt. Apparently, writers do block readers if those readers are tasked with checking a foreign key constraint. And that’s not limited to situations that might lead to a violation of referential integrity – change the SQL for connection 2 to remove the second test customer instead of the first and you’ll see two transactions that should be able to execute simultaneously, yet still are blocking each other. Using snapshot isolation might not yield the concurrency gain you are hoping for!

Another bad thing is the choice of error messages. If you run the SQL above, you’ll get this error message from the second connection once the first connection has committed the changes:

Msg 3960, Level 16, State 1, Line 2

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Orders' directly or indirectly in database 'SnapshotTest' 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.

If you change the SQL in the second connection to delete the second test customer, or if you change the SQL in the first transaction to ROLLBACK rather than COMMIT the changes, the second connection will still be blocked, but after the first connection finishes, the second connection continues, and you’ll never see any warning or error message to explain why this connection was blocked. If you ever get called in to investigate slowness in a database that uses snapshot isolation, would you consider that updates to different tables might be blocking each other? Until performing the tests above, I would have started looking elsewhere! My suggestion to Microsoft would be to change the error messages – in both cases, a warning message stating that snapshot isolation has temporarily been put out of effect should be given as soon as the second connection is blocked. Once the first connection ends, the second connection should either receive the normal foreign key constraint violation error, or it should continue without further messages.

To wrap it up, we can conclude that Microsoft’s SQL Server team has been smart enough to make sure that snapshot isolation won’t allow violation of foreign key constraints, but at the price of temporarily disabling snapshot isolation and thereby reducing concurrency. And unfortunately, they have failed to make trouble-shooting easier by causing SQL Server to send a warning message to the client if this happens.

That concludes the first part of this series. In the next part, I’ll be looking at some other, more obscure sorts of referential integrity in conjunction with snapshot isolation. Feel free to experiment with the sample code above while waiting – and if you see anything worth mentioning, be sure to post a comment!