SQL not available after a failed insert with NHibernate

Question

I have an integration test that tries to insert a row with a column that is a duplicate of a unique column that another row has. To insert the row, I call this code which is found in my entities repository:

using (var transaction = rb.unitOfWork.Session.BeginTransaction())
        {
            try
            {
                ret = (Key)rb.unitOfWork.Session.Save(entity);
                transaction.Commit();
                rb.unitOfWork.Session.Clear();
            }
            catch
            {
                transaction.Rollback();
                rb.unitOfWork.Session.Clear();
                throw;
            }
        }

When this code is run with the duplicate entity, I can see the insert come through on NHibernate Profiler. Right after that statement, I see these warnings and errors:

-- statement #1 WARN: System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'UQ_Contract_C51D43DA5070F446'. Cannot insert duplicate key in object 'dbo.Contracts'. The duplicate key value is (1005171). The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.SqlClient.SqlCommand.ExecuteBatchRPCCommand() at System.Data.SqlClient.SqlCommandSet.ExecuteNonQuery() at NHibernate.AdoNet.SqlClientSqlCommandSet.ExecuteNonQuery() at NHibernate.AdoNet.SqlClientBatchingBatcher.DoExecuteBatch(IDbCommand ps)

-- statement #2 ERROR: Violation of UNIQUE KEY constraint 'UQ_Contract_C51D43DA5070F446'. Cannot insert duplicate key in object 'dbo.Contracts'. The duplicate key value is (1005171). The statement has been terminated.

-- statement #3 ERROR: could not execute batch command.[SQL: SQL not available]Could not synchronize database state with session

-- statement #4 rollback transaction

After this test, I do a lot of Query testing and everything seems to be working fine until I hit an update statement for the same repository. Here is the code that gets called:

using (var transaction = rb.unitOfWork.Session.BeginTransaction())
        {
            rb.unitOfWork.Session.SaveOrUpdate(entity);
            transaction.Commit();
            rb.unitOfWork.Session.Clear();
        }
        rb.unitOfWork.Session.Evict(entity);

Checking the profiler shows that the update gets called, bur right after that, I get these statements:

-- statement #1 WARN: System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'UQ_Contract_C51D43DA5070F446'. Cannot insert duplicate key in object 'dbo.Contracts'. The duplicate key value is (1005171). The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.SqlClient.SqlCommand.ExecuteBatchRPCCommand() at System.Data.SqlClient.SqlCommandSet.ExecuteNonQuery() at NHibernate.AdoNet.SqlClientSqlCommandSet.ExecuteNonQuery() at NHibernate.AdoNet.SqlClientBatchingBatcher.DoExecuteBatch(IDbCommand ps)

-- statement #2 ERROR: Violation of UNIQUE KEY constraint 'UQ_Contract_C51D43DA5070F446'. Cannot insert duplicate key in object 'dbo.Contracts'. The duplicate key value is (1005171). The statement has been terminated.

-- statement #3 ERROR: could not execute batch command.[SQL: SQL not available]Could not synchronize database state with session

From what I can see, it seems like the insert statement put NHibernate into a failed state. The update code above throws a GenericADOException that says SQL Not Available. I must be doing something wrong here. Should I be handling the exception differently?

Firo Firo · Accepted Answer · 2012-10-19T08:40:16

Assuming you use Identity or any other PostInsert Id generator if the insert errors it still has no id set and the next SaveOrUpdate will try to Insert it again because it thinks it is an unsaved instance, resulting in the error again.

try {
    session.Save(entity);  // has duplicate key
} catch {}

Assert(entity.Id, Key.Unsaved);

session.SaveOrUpdate(entity2); // will issue INSERT and throws again

SQL not available after a failed insert with NHibernate

1 Answers