Many transactions in the same tables,

Question

We use application in server with Sql server and it receive many SDF file from clients to update the SqlServer Data Base the update for many Tables and I use Transaction for this, the application in server used by multiple user than it possible to be two updates in the same time, and the two with transaction, the application throw an exception that table used by another transaction !!

New transaction is not allowed because there are other threads running in the session.

and the user have other task to do in the app with transaction too, for the moment if there's an update he have to wait until it finish.

Any ideas?

Update : there's some code for my fonction : this is my fonctions with transaction run in BackgroundWorker with timer :

         DbTransaction trans = ConnectionClass.Instance.MasterConnection.BeginTransaction();
           try
           {
             ClientDalc.UpdateClient(ChosenClient, trans);
             // other functions with the same transaction               

             trans.Commit();
           }
           catch (Exception ex)
           {
              trans.Rollback();
              return false;
           }

Update Client:

        public static bool UpdateClient(ClientEntity ChosenClient, DbTransaction trans)
    {

        bool retVal = true;
        string sql = "UPDATE ClientTable Set ........";



        try
        {
            using (DbCommand cmd = DatabaseClass.GetDbCommand(sql))
            {
                cmd.Transaction = trans;
                cmd.Parameters.Add(DatabaseClass.GetParameter("@Name", Client.Name));
                //
                cmd.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            retVal = false;
        }

        return retVal;
    }

If I run any thing else in the same time that BackgroundWorker is in progress I got exception even is not a transaction

ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.”

You could insert the raw SDF into a "pending" table initially and then have another process that processes these one by one to ensure that you are only ever doing one at a time. Really though this sounds like its doing what you want it to. Locking the table through the transaction until it is complete and then releasing it again. — Chris, Feb 14 '12 at 10:01
@Chris: I work with many table, than to use a panding table I don't think it will help — Akrem, Feb 14 '12 at 10:08

score 1 · Accepted Answer · edited May 23 '17 at 12:03

1

Is this a totally borked posting?

The only reference I find to that error points to Entity Framework, NOT Sql server and I ahve never seen that in sql server - in fact, sql server has no appropiate conceot for session at all. They are called Conenctions there.

SqlException from Entity Framework - New transaction is not allowed because there are other threads running in the session

In that case your description and the tagging makes ZERO sense and the onyl answer is to use EntityFramework PROPERLY - i.e. not multi threaded. Open separate sessions per thread.

edited May 23 '17 at 12:03

Community

1
1

answered Feb 14 '12 at 10:32

TomTom

61,059
10
88
148

Open separate sessions per thread ? and what I have to do if I need to update with transaction with many users in the same time ? – Akrem Feb 14 '12 at 10:38
You have seaprate transactions. And let the database do their job. Like all other people. Waht do you think how multi user databases work? Transaction isolation iscore- and multi updatesa t the same time in one tarnsaction isolates that. – TomTom Feb 14 '12 at 10:41
@Akrem I suggest you read up on the "Unit of Work" pattern: it's what EF sessions follow. – Richard Feb 14 '12 at 14:33

score 0 · Answer 2 · answered Feb 14 '12 at 17:54

Try to follow the sample C# code

using (SqlConnection con = new SqlConnection("Your Connection String"))
{
    using (SqlCommand cmd = new SqlCommand("Your Stored Procedure Name", con))
    {
        SqlParameter param = new SqlParameter();
        param.ParameterName = "Parameter Name";
        param.Value = "Value";
        param.SqlDbType = SqlDbType.VarChar;
        param.Direction = ParameterDirection.Input;
        cmd.Parameters.Add(param);
        cmd.ExecuteNonQuery();
    }
}

Try to follow sample Stored Procedure

Create Proc YourStoredProcedureName
@ParameterName DataType(Length)
As
Begin
   Set NoCount On
   Set XACT_ABORT ON
   Begin Try
      Begin Tran
         //Your SQL Code...
      Commit Tran 
   End Try

   Begin Catch
      Rollback Tran
   End Catch
End

Suggestions

Keep your transaction as short as possible.
When you are adding a record in table, it will definitely lock the resource until the transaction is completed. So definitely other users will have to wait.
In case of updation, you can use concurrency controls

Many transactions in the same tables,

2 Answers2