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

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

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



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


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