CODEWITHSUNDEEP
sql-servernhibernatetransactionsbatch-processingcommit
raro79
raro79

Reputation: 35

NHibernate transactions commit failed

In production I had an episodic timeout exception during commit an Nhibernate transaction. But the records that I expect updated are correct! In stage environment nothing to report.

Here below the application logs of my batch program.

ERROR 2015-06-25 01:32:01,165 SRV-IIS03  Commit failed
System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The    timeout period elapsed prior to completion of the operation or the server is not    responding.
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.TdsParser.TdsExecuteTransactionManagerRequest(Byte[]  buffer, TransactionManagerRequestType request, String transactionName,  TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction  transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(Transacti onRequest transactionRequest, String transactionName, IsolationLevel iso,  SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionReq uest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction  internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalTransaction.Commit()
at System.Data.SqlClient.SqlTransaction.Commit()
at NHibernate.Transaction.AdoTransaction.Commit()

ERROR 2015-06-25 01:32:01,431 SRV-IIS03  Errore imprevisto nel metodo  Run:NHibernate.TransactionException: Commit failed with SQL exception --->  System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
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.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlInternalTransaction.Commit()
at System.Data.SqlClient.SqlTransaction.Commit()
at NHibernate.Transaction.AdoTransaction.Commit()
--- End of inner exception stack trace ---
at NHibernate.Transaction.AdoTransaction.Commit()
at [omit].Run(String[] args) in [omit].cs:line 153

ERROR 2015-06-25 01:32:01,493 SRV-IIS03  Errore imprevisto nel batch    [omit]: NHibernate.TransactionException: Transaction not successfully started
at NHibernate.Transaction.AdoTransaction.CheckBegun()
at NHibernate.Transaction.AdoTransaction.Rollback()
at [omit].RollbackTransazione() in [omit].cs:line 212
at [omit].Run(String[] args) in [omit].cs:line 159
at [omit].Main(String[] args) in [omit].cs:line 29

And here below the structure of my batch

    private static ITransaction tx;

    public virtual BatchResponseStatus Run(string[] args)
    {
        var response = BatchResponseStatus.Success;

        try
        {
            //Do some stuff 

            SetTransaction();

            //Collect elements to be processed

            foreach (var item in aListOfItem)
            {
                // Stuff

                // Call methods that creates, reads  or updates records 
                // with Fluent Nhibernate or Sql Server Stored procedure

                // Write some files 

                // Zip files

                // Update DTO
            }

            tx.Commit();
        }
        catch (Exception ex)
        { 
            RollbackTransaction();
            XXRepository.NhSession.Clear();
            response = BatchResponseStatus.Error;
        }
        finally
        {
            DisposeTransaction();
        }

        return response; 
    }

    private void SetTransaction()
    {
        tx = XXRepository.NhSession.BeginTransaction(IsolationLevel.ReadUncommitted);

        if (tx == null)
                throw new Exception("Impossible create a Transaction");
    }

    private static void RollbackTransaction()
    {
        if (tx != null)
        {
            tx.Rollback();
        }
    }

I use this technologies:

Anybody can help me? Thanks.

Upvotes: 1

Views: 4702

Answers (1)

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

The timeout occurs on the connection. This means, that the connection times out, but commit continues in Sql Server. As you can see in the log, Rollback fails because the connection is already committed. IMHO, the connection timeout is not properly handled by the SqlClient implementation.

There is a forum thread where this is discussed.

Upvotes: 1

Related Questions