|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to query for transaction's isolation level...Hi all,
Is it any way to query a running transaction to see the isolation level it is using? I'm debugging a mobile .net application that access a SQL 2K database and I need to verify if it is running transaction on the desired isolation level (read uncomitted). My idea is to run a test app that left a transaction opened, and test the isolation level by running some query from the QueryAnalyzer. Any hint is welcomed Thanks in advance Sammy This is one of the rows from DBCC USEROPTIONS
Show quote "SammyBar" <sammy***@gmail.com> wrote in message news:%23Bt97iglGHA.4708@TK2MSFTNGP04.phx.gbl... > Hi all, > > Is it any way to query a running transaction to see the isolation level it > is using? I'm debugging a mobile .net application that access a SQL 2K > database and I need to verify if it is running transaction on the desired > isolation level (read uncomitted). My idea is to run a test app that left > a transaction opened, and test the isolation level by running some query > from the QueryAnalyzer. > > Any hint is welcomed > Thanks in advance > > Sammy > > > > This is one of the rows from DBCC USEROPTIONS but can I make a "DBCC USEROPTIONS" not for my own connection, but for anoter process or spid? >> This is one of the rows from DBCC USEROPTIONS Not that I know of. If you're interested in it for a specific procedure, > but can I make a "DBCC USEROPTIONS" not for my own connection, but for > anoter process or spid? you could probably jam data into a table based on SPID at the beginning of the proc, then you can query it for any active spids from other sessions. However, if you're able to modify the proc to do this, you could probably just check the proc manually to see if the default isolation level is being overriden. A In SQL Server 2005, the view sys.dm_exec_sessions (one of the replacements
for sysprocesses) shows the isolation level for every connection. -- Show quoteHTH Kalen Delaney, SQL Server MVP "SammyBar" <sammy***@gmail.com> wrote in message news:eGfdsQhlGHA.3528@TK2MSFTNGP02.phx.gbl... >> This is one of the rows from DBCC USEROPTIONS > but can I make a "DBCC USEROPTIONS" not for my own connection, but for > anoter process or spid? > > Hi, Sammy
To determine the transaction isolation level currently set for a given connection, execute the DBCC USEROPTIONS statement from that connection. Razvan  |
|||||||||||||||||||||||
Other interesting topics