how to determin the transaction isolation level for the current session?
-
A simple question, but I coouldn't figure it out so far:
Inside an SP, how can the SP knows the transaction isolation level currently being used?
-
If I'm correct ...
if you want to be sure it is being run wit isolation level x, you can set the isolation level (at the beginning) in your sp because then the scope for the usage is only the sp (and nested stuff).
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The problem here is that if the isolation level is already higher (and you don't know it) by setting the isolation level to your desired need you may inadvertently be setting it to a level lower than it was.
-
The default isolation level is READ COMMITTED. Also from BOL.
Only one of the options can be set at a time, and it remains set for that connection until it is explicitly changed.
Hence, if you don't change the isolation level in your connection or within the stored procedure, READ COMMITTED will be the default.
-
It all works fine if the procedure setting the isolation level is the first procedure called and no other procedure changes the isolation level.
Some day someone decides to take advantage of your nice procedure and writes another procedure that calls your procedure. Perhaps a third procedure is created and that calls the second that calls your original procedure.
Now your original procedure is buried in a chain of calls and the new initiating procedure sets the isolation level to Serializable not realizing that deep in the chain of events your procedure is resetting the isolation level back to Repeatable Read. So now a transaction that started as Serializable is now working at the lower level of Repeatable Read.
I try to write procedures to be as generic as possible so that they are like value added functions. Just like the built in functions of a language. I expect others to take advantage of existing procedures so that we all benefit from more and more reusable code.
I hope you can see from my example that a procedure that sets the isolation level without knowing the current isolation level may be changing the intended outcome of a transaction. To avoid the problem I try to use locking hints such as with holdlock that only effect individual statements.
Also keep in mind that changing the isolation level may inadvertently be lowering the level which jeopardizes the integrity of the transaction, setting the level to Serializable is safe but may have the unintended effect of causing unnecessary locks to be held somewhere else in the transaction.
-
Thanks to everyone.
Perhaps I didn't make me clear. What I want to know is that in a SP what is the transaction isolation level CURRENTLY being used. The isolation level is set when a client side connects to SQL server ( by ADO.net).
I know how change the isolation level in a SP. But I don't know how to check the isolation level currently being used in a session.
-
Oh, I think your were clear. It's just that no one has an answer for you. I couldn't find anything in BOL that would help.
-
The only way I've been able to figure out the current transaction isolation level has been kind of a kludge using DBCC USEROPTIONS. Check to make sure this works with isolation level set via ASP. This method works for me but has not been tested in all scenarios for useroptions.
set nocount on
Declare @isolationLevel varchar(100)
create table #useroptions
(OptionName varchar(100)
,OptionValue varchar(100)
)
insert #useroptions
exec ('dbcc useroptions with NO_INFOMSGS')
SELECT @isolationLevel = convert(varchar(100), OptionValue)
FROM #useroptions
WHERE OptionName = 'isolation level'
SET @isolationLevel = ISNULL(@isolationLevel, 'Read Committed')
print 'Isolation Level: ' + @isolationLevel
drop table #useroptions
Note that the last line is needed because DBCC USEROPTIONS does not return an isolation level row if there has not been an explicit set. I presume read committed in this case.
Hope this helps,
Scott Thornburg
-
OK, so I'm reading blogs tonight and I found an entry almost identical to one like mine above. I'm a little embarassed (because after-the-fact I'm pretty sure I ready the entry a little while ago) and I don't take without credit . . . and THEN the story gets stranger.
Louis Davidson http://spaces.msn.com/members/drsql/?partqs=cat%3DSQL+Tip&_c11_blogpart_blogpart=blogview&_c=blogpart) notes the technique in a way eerily similar to mine (o.k. #useroptions as a table name isn't so unexpected), and quotes Adam Machanic as the source. I find a newsgroup entry for Adam's entry (http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/66c200784b04b0c/8592b6571da979f4).
So credit goes there....
Then it gets strange...I find another similar entry from September 2000:
Only, it turns out that it's *my* posting from SQL 7 days. Do things get a little mixed up?
Normally I wouldn't post about something peripheral like this, but it was simply too strange for me to pass by.
Scott Thornburg
-
Thank you Scott, I will try the code.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply