|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Using TransactionScope and connection poolI 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. <d***@2bytes.co.uk> wrote in message
Show quote news:1172844466.528837.122660@p10g2000cwp.googlegroups.com... While it may be possible to run multiple inserts against the same database > 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? > 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 On Mar 2, 8:42 pm, "David Browne" <davidbaxterbrowne no potted
m***@hotmail.com> wrote: Show quote > <d***@2bytes.co.uk> wrote in message thanks David,> > news:1172844466.528837.122660@p10g2000cwp.googlegroups.com... > > > > > 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.0http://msdn2.microsoft.com/en-gb/library/ms379553(VS.80).aspx > > 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? <d***@2bytes.co.uk> wrote in message
Show quote news:1172919767.879418.109670@n33g2000cwc.googlegroups.com... Stick with TransactionScope. TransactionScope is simpler, and, for SQL > On Mar 2, 8:42 pm, "David Browne" <davidbaxterbrowne no potted > m***@hotmail.com> wrote: >> <d***@2bytes.co.uk> wrote in message >> >> news:1172844466.528837.122660@p10g2000cwp.googlegroups.com... >> >> >> >> > 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.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? > 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 On Mar 3, 7:12 pm, "David Browne" <davidbaxterbrowne no potted
m***@hotmail.com> wrote: Show quote > <d***@2bytes.co.uk> wrote in message excellent, thanks. Didn't know about the automatic promotion and was> > news:1172919767.879418.109670@n33g2000cwc.googlegroups.com... > > > > > On Mar 2, 8:42 pm, "David Browne" <davidbaxterbrowne no potted > > m***@hotmail.com> wrote: > >> <d***@2bytes.co.uk> wrote in message > > >>news:1172844466.528837.122660@p10g2000cwp.googlegroups.com... > > >> > 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.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? > > 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 worried it was adding complexity. It certainly makes coding easier. I have used the Asynchronous commands without problem too.  |
|||||||||||||||||||||||
Other interesting topics