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

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

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

Published Friday, August 25, 2006 6:17 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

 

Denis the SQL Menace said:

Hugo instead of using user options can't you use the following code?

and basically checking for transaction_isolation_level 5 from the sys.dm_exec_sessions catalogue view?

SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVELFROM
FROM sys.dm_exec_sessions
where session_id = @@SPID
August 25, 2006 12:32 PM
 

Denis the SQL Menace said:

small typo
END AS TRANSACTION_ISOLATION_LEVELFROM
should be
END AS TRANSACTION_ISOLATION_LEVEL
August 25, 2006 12:35 PM
 

Alex Kuznetsov said:

Hi Hugo,

I have a feeling that because of the temporary table #Options the trigger will recompile every time it fires, and that is bad for performance. Makes sense?
August 25, 2006 1:21 PM
 

Hugo Kornelis said:

Alex,

I don't know the exact details about what does and what doesn't trigger a recompilation, but you might well be right. I wasn't too happy with the temp table either, but I knew (note the use of past tense here) no other way to catch the transaction isolation level.

Denis,

Great suggestion! I really should find the time to study the new dynamic management views. With your code, I don't need the temp table anymore.

IF (SELECT transaction_isolation_level
   FROM sys.dm_exec_session
   WHERE session_id = @@SPID) = 5
BEGIN;
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
END;
August 25, 2006 2:02 PM
 

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

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