3

I'm working on a WPF app which needs to perform a lot of updates and inserts during a particular operation. We are using Fluent Nhibernate 3.2. I want to execute all the updates and inserts in a single transaction so that if there's any error I can roll back all the steps. We have defined commands for each of the steps. The issue is that in one of the insert commands , we have used the Parallel.Foreach loop in which we create a new stateless session per thread.

Now if I wish to wrap all these commands in a transaction , how do I do that .?

We already have a UnitOfWork implemented , but it does not allow more than one session at a time. So I thought of moving away from the UnitOfWork and directly pass the Sessionfactory to each of the commands. Every command then creates its own session and executes it. We have a command processor which executes all the commands :

public class CommandProcessor : ICommandProcessor
    {
        public void ExecuteCommands ( IEnumerable<ICommand> commands)
        {
            using (var scope = new TransactionScope(TransactionScopeOption.Required))
            {
                foreach (var command in commands)
                {
                        command.Execute(UnitOfWork.UnitOfWorkFactory.SessionFactory);
                }

                scope.Complete();
            }
        }
    }

The transaction scope doesn't seem to work as I get an error "The operation is not valid for the state of the transaction".

Is this the correct usage of the transaction scope ? Any suggestions on how I could progress ?

Thanks.

Sennin
  • 1,001
  • 2
  • 10
  • 17
  • Why don't you use the same session for all commands? – Ioannis Karadimas Feb 23 '12 at 12:01
  • I could ..had I not used the Parallel.Foreach in one of the commands. – Sennin Feb 23 '12 at 12:34
  • From what I can find out about this, you are correct in that NHibernate requires each session to run in a separate thread. Could the example here help? http://stackoverflow.com/questions/1192111/how-do-i-do-nested-transactions-in-nhibernate – Ioannis Karadimas Feb 23 '12 at 12:44
  • 1
    It should honor the transaction scope but it depends on the data provider. If you are using SQL Server, it should work fine, but doesn't specify here. Also understand that across multiple connections it will try to use the Distributed Transaction Coordinator which you may not want. – swannee Feb 24 '12 at 03:14
  • Anytime I see someone say "we implemented Unit of Work", it raises a yellow flag for me. [According to Martin Fowler] (http://martinfowler.com/eaaCatalog/unitOfWork.html), "A Unit of Work keeps track of everything you do during a business transaction that can affect the database. When you're done, it figures out everything that needs to be done to alter the database as a result of your work." That's exactly what an NHibernate session does for you. It already implements UoW. – Daniel Schilling Jul 19 '13 at 21:03
  • It also sounds like this is going to be a _huge_ transaction. I'm guessing you chose stateless sessions and Parallel.ForEach because you're modifying thousands of rows. This seems like it could be pretty bad for the scalability of the system. See the advice here: http://msdn.microsoft.com/en-us/library/ms187484%28v=sql.105%29.aspx. In particular, "Keep the transaction as short as possible", and "Access the least amount of data possible while in a transaction". – Daniel Schilling Jul 19 '13 at 21:11

1 Answers1

0

Don't do it. If you want to consider a performance problem, NHibernate provides batching strategies for MSSqlServer, Oracle and Postgres, which will deal with updates/inserts in a reasonable number of batches/db calls. Do not share session over multiple threads (you don't do it) and preferably do not use a DTS only to use Parallel.ForEach.

Scooletz
  • 183
  • 2
  • 7