title: SQL not available after a failed insert with NHibernate
body: <p>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:</p>
  
  <pre><code>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;
              }
          }
  </code></pre>
  
  <p>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:</p>
  
  <blockquote>
    <p>-- statement #1
    WARN: 
    System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'UQ_<em>Contract</em>_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)</p>
    
    <p>-- statement #2
    ERROR: 
    Violation of UNIQUE KEY constraint 'UQ_<em>Contract</em>_C51D43DA5070F446'. Cannot insert duplicate key in object 'dbo.Contracts'. The duplicate key value is (1005171).
    The statement has been terminated.</p>
    
    <p>-- statement #3
    ERROR: 
    could not execute batch command.[SQL: SQL not available]Could not synchronize database state with session</p>
    
    <p>-- statement #4
    rollback transaction</p>
  </blockquote>
  
  <p>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:</p>
  
  <pre><code>using (var transaction = rb.unitOfWork.Session.BeginTransaction())
          {
              rb.unitOfWork.Session.SaveOrUpdate(entity);
              transaction.Commit();
              rb.unitOfWork.Session.Clear();
          }
          rb.unitOfWork.Session.Evict(entity);
  </code></pre>
  
  <p>Checking the profiler shows that the update gets called, bur right after that, I get these statements:</p>
  
  <blockquote>
    <p>-- statement #1
    WARN: 
    System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'UQ_<em>Contract</em>_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)</p>
    
    <p>-- statement #2
    ERROR: 
    Violation of UNIQUE KEY constraint 'UQ_<em>Contract</em>_C51D43DA5070F446'. Cannot insert duplicate key in object 'dbo.Contracts'. The duplicate key value is (1005171).
    The statement has been terminated.</p>
    
    <p>-- statement #3
    ERROR: 
    could not execute batch command.[SQL: SQL not available]Could not synchronize database state with session</p>
  </blockquote>
  
  <p>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?</p>