Geek Answers Handbook

Determining transaction level in stored procedures via sql trace

I use read transactions in my asp.net application.

I suspect that somehow, when I get to reading SQL Server, it is not used.

Is there a way to determine through SQL Trace what level of transaction isolation. All I see is the BEGINNING OF THE OPERATION

+4
source share
3 answers

One thing you can check through Profiler is the isolation level set at login. Check the Audit Entry event and enable the TextData column; this will show a collection of things that includes the level of isolation.

I am a little cavalier because I don’t know why it shows what it does - either this default SQL behavior, or completely depends on what the connection does. This will not show how this will change, although the usual traces of the state will be - although sifting through thousands of events for this can be a big pain.

+3
source

I am not sure if this is possible, although profiler. However, you can try the following ...

  • Open sql management studio
  • Run the command 'begin trans'
  • insert / update some data in the table
  • Select recently inserted / updated data without explicit transfer of the above protocol
  • You should not see newly inserted / updated data until you commit it.
  • This proves that you are using a read isolation level.
+2
source

If you look at a certain part of your code and can play a little with it, you can test by throwing DBCC in USER during the transaction in question - this returns the current value of the isolation level. This can be useful if you have some of the code littered with the SET TRANSACTION ISOLATION LEVEL commands - sprinkle liberally with USEROPTIONS DBCC to find out how your isolation level is set from transaction to transaction.

0
source

More articles:


All Articles