THE SQL Server Blog Spot on the Web

Welcome to - 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 1)

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

Published Friday, July 21, 2006 8:40 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



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

Part two of this series is now online. I expect to do at least two more entries, though I don't know when.
July 26, 2006 11:13 AM

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

Part three of this series is now also online.
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

Louis Davidson said:

Very nice.  I hadn't noticed this one myself.  I had warned against using snapshot for writers anyhow, though this makes it a bit more safe anyhow.  If you are using triggers for any data validation, it wouldn't be so smart.
September 18, 2006 6:12 PM

Hugo Kornelis said:

Hi Louis,

Thanks for your comment. :-)

You're right about the trigger not being so smart - but the person writing the trigger can be. I've covered that in parts two and three (but I bet you've already found them).
September 21, 2006 12:34 PM

Tony Rogerson said:

Hi Hugo,

I wish I'd come across this entry, it answers a question that I've just blogged about myself - I wondered why the FK look up still blocked with READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION and reading your entry it makes complete sense.

My particular problem was to do with when the table is a heap you don't block, when its clustered you do block which is the behaviour I go through.

Good work Hugo!!


March 30, 2007 10:24 AM

Tony Rogerson's ramblings on SQL Server said:

In this entry I look at Foreign Key look ups and why you get blocking when the referenced table has a clustered index and no blocking when the table is a heap. I use Profiler to show locks acquired / released and we look at DBCC PAGE to identity what

March 30, 2007 10:45 AM

Kalen Delaney said:

After reading Hugo’s post about when snapshot isolation doesn’t really live up to its promise , I decided

May 23, 2007 9:33 AM

mjswart said:

Extremely late comment, but I'm curious. You said: "writers do block readers if those readers are tasked with checking a foreign key constraint."

If they're tasked with checking a foreign key constraint, (connection two in your example) doesn't that make them a writer? In my head Readers are connections that only do SELECTs and writers are connections that do INSERT/UPDATE/DELETE.

March 30, 2010 1:53 PM

tobi said:

This blocking problem is not due to snapshot isolation. In all isolation levels, the "parent" end of an FK constraints must be stabilized for the duration of the DML statement.

July 10, 2012 6:36 PM

Leave a Comment


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