2

I have a problem with multithreaded transaction and entity framework. I have a thread, which operates in transaction and I would like to have a few more worker threads working within same transaction. The following code illustrates situation (there is one dummy entity in EF context, the code basically spawns 5 threads, I would like to insert some entities within each thread and at the end in main thread, I would like to continue working with DB, but to keep whole process isolated in ONE transaction):

using(var scope = new TransactionScope()) { int cnt = 5; ManualResetEvent[] evt = new ManualResetEvent[cnt]; for(int i = 0; i < cnt; i++) { var sink = new ManualResetEvent(false); evt[i] = sink; var tr = Transaction.Current.DependentClone( DependentCloneOption.BlockCommitUntilComplete); Action run = () => { using (var scope2 = new TransactionScope(tr)) { using (var mc = new ModelContainer()) { mc.EntitySet.Add(new Entity() { MyProp = "test" }); mc.SaveChanges(); } } sink.Set(); }; ThreadPool.QueueUserWorkItem(r => run()); } ManualResetEvent.WaitAll(evt); using (var mc = new ModelContainer()) { Console.WriteLine(mc.EntitySet.Count()); } Console.ReadKey(); }

The problem is, that exception is thrown on mc.SaveChanges();. Inner exception is TransactionException: "The operation is not valid for the state of the transaction." It seems that at some point, transaction is aborted. I think it is after first thread calls SaveChanges(), but Im not sure. Any idea why transaction is aborted?

Improve this question

2 Answers 2

Reset to default
1

I discovered what was the problem happening here. Based on this article, I found that it is impossible to work with two MSSQL server connections within one transaction at the same time. I also discovered that I was not properly handling dependent transaction in previous code. My working illustration code follows:

 class Context { public ManualResetEvent sink; public DependentTransaction transaction; } static object syncRoot = new object(); static void Main(string[] args) { using (var scope = new TransactionScope()) { int cnt = 5; ManualResetEvent[] evt = new ManualResetEvent[cnt]; for (int i = 0; i < cnt; i++) { var sink = new ManualResetEvent(false); evt[i] = sink; var context = new Context() { // clone transaction transaction = Transaction.Current.DependentClone(DependentCloneOption.BlockCommitUntilComplete), sink = sink }; ThreadPool.QueueUserWorkItem(new WaitCallback(Run), context); } // wait for all threads to finish ManualResetEvent.WaitAll(evt); using (var mc = new ModelContainer()) { // check database content Console.WriteLine(mc.EntitySet.Count()); } // after test is done, the transaction is rolled back and the database state is untouched Console.ReadKey(); } } static void Run(object state) { var context = state as Context; // set ambient transaction Transaction oldTran = Transaction.Current; Transaction.Current = context.transaction; using (var mc = new ModelContainer()) { mc.EntitySet.Add(new Entity() { MyProp = "test" }); // synchronize database access lock (syncRoot) { mc.SaveChanges(); } } // release dependent transaction context.transaction.Complete(); context.transaction.Dispose(); Transaction.Current = oldTran; context.sink.Set(); } }

It is probably not very nice way of writing multithreaded bussiness layer, but this shared transaction approach is WERY useful for testing in my case. The only modification need to make this work is to override Db context and synchronize save method in test runs.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

A SqlConnection is not thread-safe (and an EF ObjectContext/DbContect isn't thread-safe as well), so this will only work when you synchronize the access to the context and connection. You coming up with a model where you process the CPU intensive stuff in parallel and write all changes in one thread after all threads have finished.

Improve this answer

5 Comments

But the thing I do not understand is, that If I execute this code without transaction, it works. I'm creating new db context per thread and DependentTransaction is, as I believe, supposed to be used to share transaction across threads - link
After inspecting you code again more closely, I see what you are doing. This might indeed work, but I still find this a scary way of developing your system. What is the problem you are trying to solve?
This is not how my production code works. My goal is to do some kind of unit-integration test. I'am testing business logic layer against MSSQL server. In the test init method, I'm starting a transaction, then I'am running tested code and then I inspect database content. After test is done, I'am rolling back the transaction to keep database in original state. This method works perfectly for single threaded operations. But when operation uses more then one thread, I need to share transaction within these threads. Best without need to change production code.
I still don't understand why you need multiple transactions. One business operation should typically run on a single thread, or when multiple threads are envolved, the would be joined to have a single thread communicate with this database. That business operation is the unit of work that should be transactional. Every unit test should test a single business operation and this test would therefore be the boundary of a transaction.
Ok, maybe thats poor test design. My point was to test whole system together (smaller pieces are already tested). My application processes big ammount of data, so I separated processing to more threads, I do not want to join database interaction to one thread for performance reasons. You are right that Im going against unit testing priciple of testing small pieces of code. My purpose is more integration test. Plus, Im having the transaction ONLY to keep database state untouched after test. It is more technical then architecture question, Iam still wondering why the transaction is aborted..

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.