NHibernate, MySQL, InnoDB and nested transactions

Question

I'm using NHibernate to access an MySQL database using InnoDB tables. I know InnoDB doesn't support nested transactions, but I keep thinking things would have been much simpler if it did.

Take a look at this method for instance.. SessionManager opens a new session per thread. If I didn't store the transaction, it previous would have been automatically commited by mysql when opening a new.

Is this just an ugly workaround, or the only way? And do you have better ways of doing this?

protected override void DoCommand(ICommand command)
{
    // Open session and/or transaction if necessary
    var repoCommand = command as RepositoryCommand;
    if (repoCommand != null)
    {
        foreach (Type type in repoCommand.PersistenceTypes)
        {
            if (!_sessions.ContainsKey(type))
            {
                var session = SessionManager.GetSession(type);
                _sessions[type] = session;

                // Several types might share the same session,
                // so we'll only create one transaction
                // per session instead of per type
                // InnoDB: Gimme nested transactions!
                if (!_transactions.ContainsKey(session))
                {
                    _transactions[session] = session.BeginTransaction();
                }
            }
        }
    }

    base.DoCommand(command);
}

Edit: Some more details about the project..

The application connects to several databases using NHibernate.Burrow. Each thread has it's own Session

Each command has a Do and Undo method, and I'm planning to add a CanExecute method to verify prerequesites like permissions on network etc. Each command does a fairly small amout of work on a single subssystem, and some connects to external systems.

This class will try to run every command in it's list, and if it fails, rollback the changes. The RepositoryCommand is a command that works with NHibernate. The commands in the list might link to several different databases and external systems which must all run to keep the data in a consistent state.

Answer 1

A transaction should be a short-lived object. NHibernate doesn't support nested transactions either, so that wouldn't help.

You should rethink the way you're handling transactions; I suggest you read about the Unit of Work pattern.