How much can I put into one transaction?

Question

This builds on "Should I always use transactions in nhibernate (even for simple reads and writes)? ".

The gist is, we should always use a transaction, even for a simple get like this:

using (ITransaction transaction = session.BeginTransaction())
{
    var printerJob2 = (PrinterJob) session.Get(typeof (PrinterJob), id);
    transaction.Commit();

    return printerJob2;
}

Let's consider this code:

User user = session.Get<User>(userId);
if(user == null) return UnAuthorizedResult();

Order order = session.Get<Order>(orderId);
if(order == null) return BadRequestResult();

session.Delete<Order>(order);
transaction.Commit();
return OkResult();

I am assuming that I am not meant to create a new transaction for every DB access, as that becomes quite messy:

User user;
using (ITransaction transaction = session.BeginTransaction())
{
    user =  session.Get<User>(userId);
    transaction.Commit();
}
if(user == null) return UnAuthorizedResult();

Order order;
using (ITransaction transaction = session.BeginTransaction())
{
    order = session.Get<Order>(orderId);
    transaction.Commit();
}
if(order == null) return BadRequestResult();

using (ITransaction transaction = session.BeginTransaction())
{
    session.Delete<Order>(order);
    transaction.Commit();
    return OkResult();
}

I am assuming it's all supposed to go into one transaction. Are there limits to this?
How much can I put into one transaction before it "breaks down" in one way or another?

Answer 1

You use transactions when you want a bunch of updates to all succeed or all fail. Generally things are starting to move away from it as a modus operandi, and towards operations that are tolerant of failures, pick up where they left off if tried again (rather than inserting duplicate records - look up idempotency) etc and are generally more forgiving of a world full of latency, transient and fickle network reliability, disparate systems and the inability to establish unifying transactions (look up sagas) etc but in essence, you use a transaction when you want it to be as if it were a one hit operation that worked out, or not.

There's no point making a transaction for a read, and there's nearly no point using one for a single update (unless you want to be able to undo that update later), but if you had to collect a whole load of data about a person and insert it into 17 different tables, you might use a transaction to ensure it all worked or all failed, so you don't have partial data lying around. You don't need to worry about unpicking 9 out of the 17 inserts if there was a fail; you just roll back the transaction and it's as it the insert never happened, though autoincrement numbers might remain bumped on by one

Are there limits to how much data you can ball up in a transaction? Technically yes but I think it unlikely you'd breach them with an enterprise DB if you're adhering to the next rule of thumb, which is..

Realistically, you probably want to keep the transaction sets as small as possible to leave someone the minimum headache of sorting things out when it goes wrong. Don't save every new customer you acquire for a whole year in the same transaction and then then decide to commit the transaction just because it's Christmas; ten thousand inserts thwarted by one name that's too long or a server crash on Christmas eve isn't ideal

Answer 2

So that code from your example is kind of a mess littered with transactions opening and closing within a given controller method. The code in your example is basically the same as using no transactions because each step commits itself. I'm assuming and MVC app because I see an OKResult near the end of your code.

Transactions are atomic units of work. If you've got 3 steps and one fails everything should be rolled back to the last known state before the transaction started. In a web scenario this is usually the request. You'll see a lot of information is you google "session per request" nhibernate. With that said I do a couple of things to ensure I'm adhering to this pattern.

In global.asax I have these helper methods

    public static ISession CurrentSession
    {
        get { return (ISession) HttpContext.Current.Items[Sessionkey]; }
        private set { HttpContext.Current.Items[Sessionkey] = value; }
    }

    protected void Application_BeginRequest() { CurrentSession = SessionFactory.OpenSession(); }

    protected void Application_EndRequest()
    {
        if (CurrentSession != null)
            CurrentSession.Dispose();
    }

Then I also have this Attribute that I can used at the method or controller level to ensure each controller action is transactionally sound

[AttributeUsage(AttributeTargets.Method | AttributeTargets.Class)]
public class TransactionAttribute : ActionFilterAttribute
{
    private ITransaction Transaction { get; set; }

    public override void OnActionExecuting(ActionExecutingContext filterContext)
    {
        Transaction = MvcApplication.CurrentSession.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
    }

    public override void OnActionExecuted(ActionExecutedContext filterContext)
    {
        if (!Transaction.IsActive)
            return;

        if (filterContext.Exception == null)
        {
            Transaction.Commit();
            return;
        }

        Transaction.Rollback();
    }
}

And now my transaction code fairly well contained and doesn't have to be littered all over your controller methods.