Prev: Locks
Next: "Timeout expired" when querying linked server
From: Scott on 24 Jul 2008 14:50
There is no begin transaction call pior to calling the Insert() method.
This code was written in C# using SMO (SQL Manager Objects).
The transaction begins when the first line is called:
using (TransactionScope scope = new
TransactionScope(TransactionScopeOption.RequiresNew))

I did neglect to mention that the Insert method uses the C# key word "lock"
at the beginning of the method which is used to ensure that the block of code
runs to completion without interruption by other threads.

"Andrew J. Kelly" wrote:

> I was assuming you were calling the INSERT() method 50 times, is that not
> the case? I was also assuming the TransactionScope scope = new line of code
> started an Explicit transaction. But what I didn't catch the first time is
> that you also have this at the end of the method scope.Complete();. I
> don't know what that does but now I again assume it issues a COMMIT TRAN. If
> these assumptions are correct each call should begin a tran, Issue the
> updates and then commit the tran. Normally that would be fine and there
> should be no issues. But if you issued a Begin tran before you called the
> first Insert() all of those would be wrapped in one big nested transaction
> with none of the inner ones actually committing until the outmost one is
> committed. If you have Implicit Transactions turned on for that connection
> you might see this behavior. I would run a trace at the statement level to
> see what is going on. Also if you select @@TRANCOUNT in your Insert() method
> you can see if the level is where you expect it to be. One other note is
> that since you appear to be updating thru two connections at the same time
> you are most likely also wrapped in a distributed transaction as well.
>

From: Andrew J. Kelly on 24 Jul 2008 15:03
I would still verify that SMO or the driver is not doing an Implicit Begin.
You won't see the BEGIN TRAN in the code or the trace but you may see the
SET IMPLICIT TRANS ON command when it connects if you are tracing the
correct events. You can also verify the @@TRANCOUNT levels at the various
states to make sure as well.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Scott" <Scott(a)discussions.microsoft.com> wrote in message
news:FB1C822D-10B4-4454-B083-8C7D40DE77E5(a)microsoft.com...
> There is no begin transaction call pior to calling the Insert() method.
> This code was written in C# using SMO (SQL Manager Objects).
> The transaction begins when the first line is called:
> using (TransactionScope scope = new
> TransactionScope(TransactionScopeOption.RequiresNew))
>
> I did neglect to mention that the Insert method uses the C# key word
> "lock"
> at the beginning of the method which is used to ensure that the block of
> code
> runs to completion without interruption by other threads.
>
> "Andrew J. Kelly" wrote:
>
>> I was assuming you were calling the INSERT() method 50 times, is that not
>> the case? I was also assuming the TransactionScope scope = new line of
>> code
>> started an Explicit transaction. But what I didn't catch the first time
>> is
>> that you also have this at the end of the method scope.Complete();. I
>> don't know what that does but now I again assume it issues a COMMIT TRAN.
>> If
>> these assumptions are correct each call should begin a tran, Issue the
>> updates and then commit the tran. Normally that would be fine and there
>> should be no issues. But if you issued a Begin tran before you called the
>> first Insert() all of those would be wrapped in one big nested
>> transaction
>> with none of the inner ones actually committing until the outmost one is
>> committed. If you have Implicit Transactions turned on for that
>> connection
>> you might see this behavior. I would run a trace at the statement level
>> to
>> see what is going on. Also if you select @@TRANCOUNT in your Insert()
>> method
>> you can see if the level is where you expect it to be. One other note is
>> that since you appear to be updating thru two connections at the same
>> time
>> you are most likely also wrapped in a distributed transaction as well.
>>
>

First  |  Prev  | 
Pages: 1 2
Prev: Locks
Next: "Timeout expired" when querying linked server