The server failed to resume the transaction the transaction

Question

Im writting a webservice method for inserting into 2 tables. Here's my code:

MLL_Result _paClient = new MLL_Result();

    lock (thisLock)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["Main.ConnectionString"]))
        {
            connection.Open();
            SqlTransaction transaction;
            transaction = connection.BeginTransaction("SampleTransaction");

            try
            {

                // Do some insert / update here 
                    #region FIRST
                    Tbl_Patient patientObject = new Tbl_Patient();
                    string[] spID = sid.Split('-');
                    patientObject.PID = pid;
                    patientObject.SID = sid;
                    patientObject.Seq = spID[1];
                    patientObject.Address = address;
                    patientObject.Phone = phone;
                    _paClient.Insert(patientObject, connection, transaction);


                    #endregion
                    /////
                    #region LAST

                    if (_interClient.CheckExist(patientObject.PID) == 0)
                    {
                           Tbl_Inter inteObject = new Tbl_Inter();
                           inteObject.PID = patientObject.PID;
                           inteObject.Address = patientObject.Address;
                           inteObject.DateIN = patientObject.DateIN;
                          _paClient.Insert(inteObject, connection, transaction);
                    }


                    #endregion   
                }


               transaction.Commit(); 

            }
            catch (Exception ex)
            {
                #region Catch Exception

                //Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                //MessageBox.Show("  Message:" + ex.Message);

                // Attempt to roll back the transaction. 
                try
                {
                    transaction.Rollback();
                }
                catch (Exception ex2)
                {
                    // This catch block will handle any errors that may have occurred 
                    // on the server that would cause the rollback to fail, such as 
                    // a closed connection.
                    //Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                    //MessageBox.Show("  Message:" + ex2.Message);
                }
                #endregion
            }
        }

It does work fine when the traffic is low. But when the traffic high, it generates error like this:

The server failed to resume the transaction Desc ... The transaction active in the session has been commited or aborted by another session.

Please help me solve this problem plz :(( and does using both lock and transaction like this conflict each other ??

Answer 1

A few things jump out at me.

You might have a serialization problem related to the transaction, where you're inserting a value on a transactional connection, then checking for the existence of the value you just inserted on a different non-transaction connection. If your transaction isolation level is set to "serializable," the read op will be blocked until it times out.

In any case, the best practice with any kind of "scarce" resource management (in this case, database transactions) is to acquire late and release early. You want to spend as little time and as few CPU cycles as possible between beginning the transaction and committing it (or rolling it back). The entire time the transaction is open, the database server is synchronizing access to resources, locking ranges of tables, etc.

So you might consider populating your DTO's outside the transaction instead of inside of it.

I presume that _paClient and _interClient are both member variables of whatever class this code is in? I see you passing a connection to _paClient, but not to _interClient, so _interClient cannot be involved in the same transaction as _paClient. Where is _interClient getting its database connection from? If _interClient is spinning up its own database connection, does the database you're using adequately support more than one connection per client? Spinning up a new connection after starting a transaction on the original connection might be screwing up your original connection. I have no idea what database you're using or what kind of behavior this sort of thing might cause in the driver stack for that database.

Is lock (thisLock) really necessary here? In general, locking does not help performance. But if it's necessary, then it's necessary.

The nested try/catch is probably overkill. You'll have exception handling and logging at the edge of your call stack that will catch a failure here, right?

I would consider a somewhat different structure for the actual transaction management, maybe more like the reworked code below.

Note that you could leave the explicit rollback code out and let the Transaction object's Dispose() method take care of the rollback, but that that behavior is provider-specific (it works properly with SQL Server) and you may not be able to rely 100% on it.

Tbl_Patient patientObject = new Tbl_Patient();
string[] spID = sid.Split('-');
patientObject.PID = pid;
patientObject.SID = sid;
patientObject.Seq = spID[1];
patientObject.Address = address;
patientObject.Phone = phone;

Tbl_Inter inteObject = null;
// does this CheckExist function have to hit the database, or can it avoid that?
if (_interClient.CheckExist(patientObject.PID) == 0)
  {
  inteObject = new Tbl_Inter();
  inteObject.PID = patientObject.PID;
  inteObject.Address = patientObject.Address;
  inteObject.DateIN = patientObject.DateIN;
  }

using (var con = new SqlConnection(ConfigurationSettings.AppSettings["Main.ConnectionString"]))
  {
  con.Open();
  using( var tx = con.BeginTransaction("SampleTransaction") )
    {
    try
      {
      _paClient.Insert(patientObject, con, tx);
      if( null != inteObject )
        {
        _paClient.Insert(inteObject, con, tx);
        }
      tx.Commit();
      }
    catch( Exception ex )
      {
      try{ transaction.Rollback(); }
      catch( Exception ex2 )
        {
        // lame that this is necessary if you're not using implicit rollback
        // write to log, whatever...
        }
      throw; //re-throw the original exception w/call stack for logging by your global exception handler
      }
    }
  }