Sql Server / VS.net 2005 debugger transaction lock problem

Hello usenet,

I am currently debugging an application that uses SqlTransactions. When
running in the debugger, it seems that my transaction ignores the
isolation level and locks everything in SQL Server...

If I put a breakpoint before SqlTransaction.Commit gets called, all the
tables used throughout the transaction are locked (ie. I can't select
anything in a separate Query Analyzer window.)

Is there a way my transaction can honor its isolation level, or am I
doomed to annoy the hell of my coworkers when I'm debugging some code?

Thanks for your time!

Ben
Fri May 12 13:24:02 CDT 2006

"Florian Doyon" <florian.doyon@gmail.com> wrote in message
news:1147443578.548217.34930@j33g2000cwa.googlegroups.com...
> Hello usenet,
>
> I am currently debugging an application that uses SqlTransactions. When
> running in the debugger, it seems that my transaction ignores the
> isolation level and locks everything in SQL Server...
>
> If I put a breakpoint before SqlTransaction.Commit gets called, all the
> tables used throughout the transaction are locked (ie. I can't select
> anything in a separate Query Analyzer window.)
>
> Is there a way my transaction can honor its isolation level, or am I
> doomed to annoy the hell of my coworkers when I'm debugging some code?

If you are debugging on the live database you are doomed to be more than
just an annoyance, sooner or later (if you have a really good trigger/stored
procedure system you'll be able to undo the damage).

Make a copy of (part of) the database for debugging purposes, then you won't
affect anyone but yourself. Still won't make Query Analyzer work for you,
though.

>
> Thanks for your time!
>

Florian
Sat May 13 09:42:42 CDT 2006

Thanks for the tip,

But I'd be glad if someone could explain me why VS.Net's debugger
alters the SqlTransaction behavior before I resort to using the very
excellent Reflector ;)

Cheers!