Transactions in NHibernate - UPDATE then INSERT. What am I doing wrong?

Question

In this sample console app I want to update a row in a table, and then insert another row in the same table.

The table is like this

CREATE TABLE [dbo].[Basket2](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NULL
) ON [PRIMARY]


CREATE UNIQUE NONCLUSTERED INDEX [IX_Basket] ON [dbo].[Basket2] 
(
    [UserId] ASC
)

So basically a user cannot have 2 baskets.

For reasons beyond this post baskets must not be deleted from the table. Therefore when a user needs a new basket the old one is just set to a unique number (id*-1).

The following code is a sample app that simulates the flow - and fails

private static void Main(string[] args)
    {
        ISessionFactory sessionFactory = CreateSessionFactory();

        int userId = new Random().Next();
        int basketId;
        using (var session = sessionFactory.OpenSession())
        {
            using (var tx = session.BeginTransaction(IsolationLevel.ReadUncommitted))
            {
                var newBasket = new Basket {UserId = userId};

                basketId = (int) session.Save(newBasket);
                tx.Commit();
            }

            using (var tx = session.BeginTransaction(IsolationLevel.ReadUncommitted))
            {
                var basket = session.Get<Basket>(basketId);
                basket.UserId = basket.Id*-1;
                session.Save(basket);

                // comment in this line to make it work:
                //session.Flush();

                var newBasket = new Basket {UserId = userId};
                session.Save(newBasket);
                tx.Commit();
            }
        }
    }

The error is:

Unhandled Exception: NHibernate.Exceptions.GenericADOException: could not insert: [ConsoleApplication1.Basket][SQL: INSERT INTO [Basket] (UserId) VALUES (?); select SCOPE_IDENTITY()] ---> System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.Basket' with unique index 'IX_Basket'.

If I Flush the session (commented out lines) it works, but why is this necessary?

I would prefer not having to Flush my session and letting Commit() handle it.

Answer 1

You don't need to Save / Update / SaveOrUpdate any entities which are already in the session.

But you are reusing the same id again. So make sure that the session is flushed before:

       using (var tx = session.BeginTransaction(IsolationLevel.ReadUncommitted))
        {
            var basket = session.Get<Basket>(basketId);
            basket.UserId = basket.Id*-1;

            // no save
            //session.Save(basket);

            // flush change on unique field
            session.Flush();

            var newBasket = new Basket {UserId = userId};

            // save new item which is not in the session yet
            session.Save(newBasket);
            tx.Commit();
        }

This is because you add the same unique value again. Of course you change the existing value before, but this is not stored to the database before the session is flushed.

The session is flushed when:

• you call flush
• before queries (except of Get and Load)
• on commit (except you use your own ADO connection)

It is a common misunderstanding that NH performs update or insert on the database when you call Save or Update. This is not the case. Insert and update are performed when flushing the session. (There are some exceptions on that, eg. when using native ids.)