Using TransactionScope and connection pool

[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.

 

[David Browne]

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?

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

 

[deja]

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

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?

 

[David Browne]

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?

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

 

[deja]

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

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.