Duplicate entry 'XXX' for key 'PRIMARY' in NHibernate Aurora (MySQL)

Question

My Controller:

using (ISession session = NHibernateSessionPerRequest.GetCurrentSession())
{
    using (ITransaction transaction = session.BeginTransaction())
    {
        try
        {
            // Changed for the sake of simplicity.
            // What I meant was entities being saved without using Flush()

            var asset = new Asset() { name = "test" };
            session.Save(asset);

            var story = new Story();
            session.Save(story);

            asset.stories.Add(story);
            session.Save(asset);

            var color = new Color() { name = colorName };
            color.asset = asset;
            session.Save(color);

            transaction.Commit();

            return Json(new { Status = "OK" });
        }
        catch (Exception ex)
        {
            if (!transaction.WasCommitted)
            {
                transaction.Rollback();
            }

            return Json(new { Status = "NOK", Mensagem = ex.Message, });
        }
    }
}   

My Mappings:

public AssetMap()
{
    Id(x => x.id).GeneratedBy.Increment();
    Map(x => x.name);
    HasMany(x => x.stories);
}

public StoryMap()
{
    Id(x => x.id).GeneratedBy.Increment();
}

public ColorMap()
{
    Id(x => x.id).GeneratedBy.Increment();
    Map(x => x.name);
    References(x => x.asset);
}

All id columns are auto increment.

Everything seems fine, but EVERY ONCE IN A WHILE I get this error:

Duplicate entry 'XXX' for key 'PRIMARY' transaction could not insert: [K1.Domain.Story#XXX][SQL: INSERT INTO Story (id) VALUES (?)]

OR

Duplicate entry 'YYY' for key 'PRIMARY' transaction could not insert: [K1.Domain.Color#YYY][SQL: INSERT INTO Color (name, asset_id, id) VALUES (?, ?, ?)]

The error never happens with Asset (first entity saved), just with Story or Color entities.

The error message is absolutely correct, since it does exist in the database a Story with id = XXX (or Color with id = YYY), added by another user seconds before me.

Background info:

• Seems to me it's a concurrency issue. I know ISession isn`t thread safe, but it looks lame MySQL or NHibernate doesn't know how to treat it. So, I am pretty sure I am doing something wrong.

• I think the error occurs intermittently just because not always there will be another Story or Color with the same id MySQL/NHibernate tries to use.

• We migrate from SQL Server and our code was working perfectly fine. We spent the last 3 days digging into this without success.

• I am not setting any ID. I don't know if it makes sense... But I think NHibernate gives Color/Story an ID (with is available at the moment), but, when I commit the transaction, the ID isn't available anymore.

One more thing that might help...

During my last test, NHibernate tried to save Story with ID = 320962 (which already exists) and then .NET fired the exception.

Then I clicked the Save button again, and NHibernate tried to save Story with ID = 320963 (320962 + 1).

Then, I clicked for the third time the Save button and NHibernate tried to save Story with ID = 320964 (320962 + 2).

None of then worked, since all these 3 ids weren't available. Was NHibernate suppose to "remember" these ids?

Answer 1

I just needed to change my mappings to:

Id(x => x.id).GeneratedBy.Identity();

It seems MySQL handles auto increment columns in a different way SQL Server does.