ServiceStack / FluentNHibernate / MySQL - Same connection used by two concurrent requests

Question

We seem to have come up on a weird issue, where two concurrent requests to our service are actually using the same DB connection.

Our setup is ServiceStack + NHibernate + FluentNHibernate + MySQL. I have set up a small test that recreates the problem:

public class AppHost : AppHostBase
{
    private ISessionFactory _sessionFactory;

    public AppHost() : base("Lala Service", typeof(AppHost).Assembly)
    {
    }

    public override void Configure(Container container)
    {
        _sessionFactory = Fluently.Configure()

            .Database(MySQLConfiguration.Standard.ConnectionString(conn =>
                conn.Server("localhost").Username("lala").Password("lala").Database("lala")))

            .Mappings(mappings => mappings.AutoMappings.Add(
                AutoMap.Assembly(GetType().Assembly).Where(t => t == typeof(Lala))
                       .Conventions.Add(DefaultLazy.Never(), DefaultCascade.All())))

        .BuildSessionFactory();

        container.Register(c => _sessionFactory.OpenSession()).ReusedWithin(ReuseScope.Request);
    }
}

---

public class Lala
{
    public int    ID   { get; set; }
    public string Name { get; set; }
}

[Route("/lala")]
public class LalaRequest
{
}

public class LalaReseponse
{
}

---

public class LalaService : Service
{
    private ISession _session;

    public ISession Session1
    {
        get { return _session; }
        set { _session = value; }
    }

    public LalaReseponse Get(LalaRequest request)
    {
        var lala = new Lala
        {
            Name = Guid.NewGuid().ToString()
        };

        _session.Persist(lala);
        _session.Flush();

        lala.Name += " XXX";

        _session.Flush();

        return new LalaReseponse();
    }
}

The I hit this service 10 times concurrenly via Ajax like so:

    <script type="text/javascript">
        for (i = 0; i < 10; i++) {
            console.log("aa");
            $.ajax({
                url:      '/lala',
                dataType: 'json',
                cache:    false
            });
        }
    </script>

The result is consistenly:

1. Number of connections open < 10.
2. Not all records updated.
3. On occasion - a StaleObjectStateException thrown - if I delete records.

The reason behind this is that the connections are reused by two concurrent requests, and then LAST_INSERT_ID() gives the ID of the wrong row, so two requests are updating the same row.

In short: it's a complete mess and it's clearly sharing the DB connection between requests.

The question is: Why? How should I have configured things so that each request gets its own connection from the connection pool?

Answer 1

Finally solved it, what a day-waster!

The source of the problem is NHibernate's connection release mode:

11.7. Connection Release Modes

The legacy (1.0.x) behavior of NHibernate in regards to ADO.NET connection management was that a ISession would obtain a connection when it was first needed and then hold unto that connection until the session was closed. NHibernate introduced the notion of connection release modes to tell a session how to handle its ADO.NET connections. ... The different release modes are identified by the enumerated values of NHibernate.ConnectionReleaseMode:

• OnClose - is essentially the legacy behavior described above. The NHibernate session obtains a connection when it first needs to perform some database access and holds unto that connection until the session is closed.

• AfterTransaction - says to release connections after a NHibernate.ITransaction has completed.

The configuration parameter hibernate.connection.release_mode is used to specify which release mode to use.

...

• after_transaction - says to use ConnectionReleaseMode.AfterTransaction. Note that with ConnectionReleaseMode.AfterTransaction, if a session is considered to be in auto-commit mode (i.e. no transaction was started) connections will be released after every operation.

This got entangled together with MySQL .NET/Connector's default connection pooling, and effectively meant that the connections were swapped between concurrent requests, as one request released the connection back to the pool and the other acquired it.

However, I think that the fact that NHibernate calls LAST_INSERT_ID() after releasing and re-acquiring the connection is a bug. It should call LAST_INSERT_ID() inside the same "operation".

Anyway, solutions:

1. Use transactions, which is what we normally do, or

2. If you can't or don't want to use transactions in a certain context for some reason (which is what happened to use today), set the connection release mode to "on close". With FluentNHibernate that would be:

   .ExposeConfiguration(cfg =>
       cfg.SetProperty("connection.release_mode", "on_close"));
   

   And from here on the connection is bound to the session even if there is no transaction.