3

Hi firstly thank you for your attention on this question； Is there any way to implement a transaction like this in c#

using （Transactionscope x=new Transactionscope ())
{

   Thead A()=> Independent Transactionscope()  A（Insert into table X ）

   Thead B()=> Independent Transactionscope()  B（Insert into table Y ）

   Thead C()=> Independent Transactionscope()  C（Insert into table Z ）

   Thread.WaitAll(A,B,C)

  commit big transaction x/ rollback big transaction x
}
frank_liu
  • 31
  • 6
  • SQL commands that execute within a transaction must all execute against the same SQL connection. Therefore it makes no sense to separate them into threads; there are going to get executed in series anyway. – John Wu Dec 19 '19 at 05:38
  • 1
    @JohnWu on .Net Framework, TransactionScope can be used to initiate Distributed Transactions via MS DTC, so there is no need for all commands to be associated with the same Sql Connection. In fact, TransactionScope can be used for other ACID resources, such as queues or transactional file systems. – StuartLC Dec 19 '19 at 05:43
  • @StuartLC Interesting, and clever. It didn't occur to me to tie in MSDTC. Question: If OP were to do that for (sub)transactions in the same database instance, wouldn't there be potential for the threads to deadlock each other? – John Wu Dec 19 '19 at 22:38
  • @JohnWu yes, DTC can lead to a lot of locking related issues and often reduce the scalability of an enterprise. Seemingly nowadays most folk prefer looking at compensated transactions, or moving the source of truth out of RDMS into events and commands (Actor Model,Event Sourcing and such) – StuartLC Dec 20 '19 at 08:57

1 Answers1

2

Note that Distributed Transactions are currently not supported on .Net Core, only on .Net Framework.

In order to use a TransactionScope to span multiple threads, you'll need to use DependentClone to tie the threads into the parent TransactionScope.

The steps are:

  1. Start a TransactionScope on your main / first thread
  2. Just before creating each thread, use DependentClone to create a DependentTransaction, and then pass this DependentTransaction instance to the new thread.
  3. On the child thread, you can use the TransactionScope(DependentTransaction) constructor overload to create a linked TransactionScope, in which the child thread can perform local transactions.
  4. As the work on each child thread is completed successfully, then commit both the thread TransactionScope and the DependentTransaction
  5. On the main thread, wait until all threads are complete, and then commit the root TransactionScope

There's some caveats too:

  1. Using DependentTransaction on multiple threads will immediately require the use of MSDTC.
  2. Using multiple threads under a large DTC transaction isn't going to make insertion into the same table any quicker (Use SqlBulkCopy for that), and you'll want to measure whether parallel inserts into different tables, same database under a DTC transaction warrants the locking overhead or returns any performance benefit.
  3. If you're using async, then you'll need TransactionScopeAsyncFlowOption.Enabled

More about Transction Scope here

StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • Great! Thx your guideline~ – frank_liu Dec 19 '19 at 07:45
  • I would generally advise against DTC if possible, e.g. if you are doing a lot of connected work on the same database, then I would advise to use a Single Transaction - either use SqlTransaction on a single connection, or if using Transaction Scope with multiple (connection pool) connections, make sure that you close each connection before continuing the next one - this will typically wind up on the Lightweight Transaction manager. – StuartLC Dec 19 '19 at 08:54
  • On a single database, your time is usually better spent looking at optimising the performance on a single thread / doing the work sequentially, rather than trying to parallelize the work under an umbrella transaction. – StuartLC Dec 19 '19 at 08:54
  • `TransactionScopeAsyncFlowOption` is more for async / await purposes, since the continuation, and the final TransactionScope complete + disposal may be executed on a different thread than the one the TS started on. I have a suspicion however that async calls must complete sequentially, i.e. trying to parallelize async code (e.g. `Task.WhenAll`) will probably require the same handling as `DependentTransaction`, since the continuations could complete concurrently on different threads. – StuartLC Dec 20 '19 at 09:05