Thursday, March 28, 2013

Detecting Transaction Isolation Level through Profiler Trace and other means on MS SQL Server 2005

I doubt that this comes up often, but I find myself in a bit of an argument about what isolation level will be used for transactions in our SQL Server database when the transaction is initiated by WebSphere.  There are various documents that indicate how WebSphere can over ride this value.

I decided to dig into this a bit further.

TRACE:

In doing so, I found that I could monitor our SQL Server DB to determine this.

I have only found two places where the transaction isolation level is appearing:
1 - Sessions:Existing Connection.Text Data
2 - In a deadlock trace as part of the XML output.

If anyone can find it elsewhere, I'd love to hear from you.  However, this makes sense, since this is more-or-less controlled at the session level.

QUERY:
If found that 
DBCC USEROPTIONS
would give me the *default* TIL for the db, but that if that TIL was altered by the session, it would not reflect that.

To get the TIL for *your session*, you can run the following query:

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_LEVEL FROM sys.dm_exec_sessions

Credit to StackOverflow question and answer here: http://stackoverflow.com/questions/1038113/how-to-find-current-transaction-level 

No comments:

DBT-50000 when using DBCA.bat on Windows (Oracle 19.11)

I’ve been having some trouble getting DBCA to run in order to create databases. Thought I’d share it with you, and thus document it for la...