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 4)

This blog has moved! You can find this content at the following new location:

https://SQLServerFast.com/blog/hugo/2006/09/snapshot-isolation-a-threat-for-integrity-part-4/

Published Friday, September 15, 2006 8:58 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

 

Alex Kuznetsov said:

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?
September 20, 2006 2:45 PM
 

Hugo Kornelis said:

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?
September 21, 2006 12:42 PM
 

Alex Kuznetsov said:

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



September 26, 2006 1:30 PM
 

Fabricio said:

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...

October 14, 2009 12:12 AM
 

Hugo Kornelis said:

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!

October 25, 2009 10:17 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.
Privacy Statement