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

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

Alex Kuznetsov — Wed, 20 Sep 2006 18:45:14 GMT

Hi Hugo,

Another way would be to create an indexed view as follows:

CREATE VIEW dbo.CustomerTypeAOrders WITH SCHEMABINDING
AS
SELECT c.CustID, o.OrderId
FROM dbo.Orders o JOIN dbo.Customers c ON c.CustID = o.CustID
WHERE o.OrderType = 'A'
go
CREATE UNIQUE CLUSTERED INDEX UCI_CustomerTypeAOrders ON dbo.CustomerTypeAOrders(CustID, OrderId)
go

Modifications will serialize because they will acquire update locks on dbo.CustomerTypeAOrders. Makes sense?

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

Hugo Kornelis — Thu, 21 Sep 2006 16:42:34 GMT

Hi Alex,

Thanks for your thoughts.

I don't see how this indexed view would prevent me from entering type 'A' orders for a non-existing customer. I've tried it, and I could execute

INSERT INTO Orders (OrderID, OrderType, CustID)
VALUES ('O2', 'A', 3)

just fine. Am I missing something?

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

Alex Kuznetsov — Tue, 26 Sep 2006 17:30:17 GMT

Hi Hugo,

Sorry for my incomplete explanations. Here is a complete repro script:

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

CREATE VIEW dbo.CustomerTypeAOrders WITH SCHEMABINDING
AS
SELECT c.CustID, o.OrderId
FROM dbo.Orders o JOIN dbo.Customers c ON c.CustID = o.CustID
WHERE o.OrderType = 'A'
go
CREATE UNIQUE CLUSTERED INDEX UCI_CustomerTypeAOrders ON dbo.CustomerTypeAOrders(CustID, OrderId)
go

CREATE FUNCTION dbo.CustExists (@CustID int)
RETURNS char(1)
AS
BEGIN
DECLARE @retval char(1)
IF EXISTS (SELECT *
FROM dbo.Customers
WHERE CustID = @CustID)
SET @retval = 'Y'
ELSE
SET @retval = 'N'
RETURN @retval
END;
go
ALTER TABLE dbo.Orders
ADD CONSTRAINT TypeAMustExist
CHECK (OrderType <> 'A' OR
dbo.CustExists(CustID) = 'Y');
go

-------------------- in the first tab run this

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

-- 1. Check to see that the customer has no orders

SELECT *

FROM Orders

WHERE CustID = 1;

-- Remove the customer

DELETE Customers

WHERE CustID = 1;
--- but do not commit yet

---- 2. in the second tab run this:
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);
-- statement hangs

--- 3. open up an Activity window and note that the command is suspended

--- 4. go to first window and commit
-- note that the second window displays an error right away:

(1 row(s) affected)
Msg 3960, Level 16, State 2, Line 15
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Customers' directly or indirectly in database 'Sandbox' 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.

---- 5. Run SELECT @@SPID In both windows and make sure they match what you saw in Activity Monitor
--- 6. restore the data:
INSERT INTO Customers (CustID, CustName)
VALUES (1, 'First test customer');
--- 7. and drop the view
DROP VIEW dbo.CustomerTypeAOrders

--- now repeat steps 1 and 2 - this time you can add an orphan all right

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

Fabricio — Wed, 14 Oct 2009 04:12:52 GMT

Hugo, has snapshot got any better in SQLServer 2008 ? Since I never used it on production (when I used, was with Firebird - which record versioning implementatio came from Interbase, which first version was on mid-80s), I never though MS has screwed so much....

Sorry for writing on so old topic...

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

Hugo Kornelis — Sun, 25 Oct 2009 14:17:18 GMT

Hi Fabricio,

As far as I know, there have been no changes to snapshot in SQL Server 2008.

However, I don't consider snapshot isolation to be bad, as I don't think there are better alternatives. The only way to preserve data integrity is to check modifications against existing (other) data. If you choose to use old, possibly stale, data for this check, you run the risk of ruining integrity. And if you choose to use current data, you have no choice but to wait until pending other changes to that data have been either committed or rolled back, in other words, until locks are released. Since integrity should be the first priority, I think Microsoft made the right choice. My only problem is that the phrase "readers don't block writers" is too simplistic.

With custom-built integrity checks (triggers, CHECK constraints that call user-defined functions, etc), you get more control and more repsonsibility. You can override a snapshot setting by using SET TRANSACTION ISOLATION LEVEL or query hints to make sure that snapshot isolation, if in effect, will be bypassed for the check. Or you can choose to take the risk of checking against stale data. Your choice, and the consequences are yours as well.

Thanks for your comment!