Using TransactionScope and connection pool

  • Thread starter Thread starter deja
  • Start date Start date
D

deja

hi,

I am trying to use TransactionScope and multiple async calls but get
Transaction aborted - transaction in doubt errors.

My program goes like this

using (TransactionScope ts = new TransactionScope())
{

do an insert on Table1 (Connection1 = new SqlConnection)

foreach table to read from aList
{
doSyncRead using Connection1
}

foreach table to insert from bList
{
do AsyncInsert passing in
DependentTransaction(BlockCommitUntilComplete) using new
SqlConnection(same connection string as Connection1)
}

Wait for all Inserts to Complete (using WaitHandle.WaitAll)

do a couple more inserts using Connection1

ts.Complete()
}

Each AsyncInsert create their own TransactionScopes using the
dependent transaction and call complete when finished.

Using Profiler I can see it gets to the TM Commit and seems to bail
out at this stage. But if I change the AsyncInserts to Synchronous
Inserts it works fine. All the inserts seem to complete - it is only
at the Commit stage that it fails with TransactionInDoubt.

The TransactionScope is in a web service. The error gets returned to
the client but then there still seems to be a TM REQUEST Command on
the SQL Server which then blocks other database requests until I
restart the DTC.

Can anyone tell me what I'm doing wrong? Is there another service I
need to enable or something?

thanks
Phil
BTW I also get a Promote Tran on Connection1 (seen via Profiler) -
starts and completes.
 
hi,

I am trying to use TransactionScope and multiple async calls but get
Transaction aborted - transaction in doubt errors.

My program goes like this

using (TransactionScope ts = new TransactionScope())
{

do an insert on Table1 (Connection1 = new SqlConnection)

foreach table to read from aList
{
doSyncRead using Connection1
}

foreach table to insert from bList
{
do AsyncInsert passing in
DependentTransaction(BlockCommitUntilComplete) using new
SqlConnection(same connection string as Connection1)
}

Wait for all Inserts to Complete (using WaitHandle.WaitAll)

do a couple more inserts using Connection1

ts.Complete()
}

Each AsyncInsert create their own TransactionScopes using the
dependent transaction and call complete when finished.

Using Profiler I can see it gets to the TM Commit and seems to bail
out at this stage. But if I change the AsyncInserts to Synchronous
Inserts it works fine. All the inserts seem to complete - it is only
at the Commit stage that it fails with TransactionInDoubt.

The TransactionScope is in a web service. The error gets returned to
the client but then there still seems to be a TM REQUEST Command on
the SQL Server which then blocks other database requests until I
restart the DTC.

Can anyone tell me what I'm doing wrong? Is there another service I
need to enable or something?
Click to expand...

While it may be possible to run multiple inserts against the same database
in separate connections in a big DTC transaction, it's unlikely to improve
your performance, and has a unusually high degree of difficulty.

You can build a batch containing multiple commands, so they run serially,
but with only a single SqlCommand. Or you can execute multiple SqlCommands
simultaneously on a single SqlConnection and single transaction.

Asynchronous Command Execution in ADO.NET 2.0
http://msdn2.microsoft.com/en-gb/library/ms379553(VS.80).aspx

David
 
While it may be possible to run multiple inserts against the same database
in separate connections in a big DTC transaction, it's unlikely to improve
your performance, and has a unusually high degree of difficulty.

You can build a batch containing multiple commands, so they run serially,
but with only a single SqlCommand. Or you can execute multiple SqlCommands
simultaneously on a single SqlConnection and single transaction.

Asynchronous Command Execution in ADO.NET 2.0http://msdn2.microsoft.com/en-gb/library/ms379553(VS.80).aspx

David
Click to expand...

thanks David,

I will probably follow your suggestion using the Asynchronous Command
Execution. Would still like to know why the DTC transaction didn't
work. Also, if I am only using single connection, should I go back to
using IDbTransaction rather than TransactionScope or will there not be
too much in it?
 
thanks David,

I will probably follow your suggestion using the Asynchronous Command
Execution. Would still like to know why the DTC transaction didn't
work. Also, if I am only using single connection, should I go back to
using IDbTransaction rather than TransactionScope or will there not be
too much in it?
Click to expand...

Stick with TransactionScope. TransactionScope is simpler, and, for SQL
Server, will create a lightweight promotable transaction. So you get the
the best of both worlds. A simple, local SqlTransaction if you only enlist
work from one SqlConnection, and automatic promotion to a DTC transaction if
you enlist work on other transactions, or other transaction-aware
components.

David

David
 
Stick with TransactionScope. TransactionScope is simpler, and, for SQL
Server, will create a lightweight promotable transaction. So you get the
the best of both worlds. A simple, local SqlTransaction if you only enlist
work from one SqlConnection, and automatic promotion to a DTC transaction if
you enlist work on other transactions, or other transaction-aware
components.

David

David
Click to expand...


excellent, thanks. Didn't know about the automatic promotion and was
worried it was adding complexity. It certainly makes coding easier. I
have used the Asynchronous commands without problem too.
 
Back
Top