Multiple Insert and Update causing DeadLock c#

Question

I have two parts to my application which both does massive amount of insert and update respectively and because or poor managemenent, there's deadlock.

I am using entity framework to do my insert and update.

The following is my code for my TestSpool program. The purpose of this program is to insert x number of records with a given interval.

using System;
using System.Linq;
using System.Threading;
using System.Transactions;
namespace TestSpool
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new TestEntities())
            {
                decimal start = 700001;
                while (true)
                {
                    using (TransactionScope scope = new TransactionScope())
                    {
                        //Random ir = new Random();
                        //int i = ir.Next(1, 50);
                        var objs = db.BidItems.Where(m => m.BidItem_Close == false);
                        foreach (BidItem bi in objs)
                        {
                            for (int j = 0; j <= 10; j++)
                            {
                                Transaction t = new Transaction();
                                t.Item_Id = bi.BidItemId;
                                t.User_Id = "Ghost";
                                t.Trans_Price = start;
                                t.Trans_TimeStamp = DateTime.Now;
                                start += 10;
                                db.Transactions.AddObject(t);

                            }
                            Console.WriteLine("Test Spooled for item " + bi.BidItemId.ToString() + " of " + 11 + " bids");
                            db.SaveChanges();
                        }

                        scope.Complete();
                        Thread.Sleep(5000);
                    }
                }
            }
        }
    }
}

The second part of the program is the testserverclass, the serverclass supposed to processed a huge amount of transactions from testspool and determined the highest amount of the transaction and update to another table.

using System;
using System.Linq;
using System.Transactions;
public class TestServerClass
{

    public void Start()
    {
        try
        {

            using (var db = new TestServer.TestEntities())
            {

                while (true)
                {
                    using (TransactionScope scope = new TransactionScope())
                    {
                        var objsItems = db.BidItems.Where(m => m.BidItem_Close == false);
                        foreach (TestServer.BidItem bi in objsItems)
                        {
                            var trans = db.Transactions.Where(m => m.Trans_Proceesed == null && m.Item_Id == bi.BidItemId).OrderBy(m => m.Trans_TimeStamp).Take(100);

                            if (trans.Count() > 0)
                            {
                                var tran = trans.OrderByDescending(m => m.Trans_Price).FirstOrDefault();

                                // TestServer.BidItem bi = db.BidItems.FirstOrDefault(m => m.BidItemId == itemid);
                                if (bi != null)
                                {
                                    bi.BidMinBid_LastBid_TimeStamp = tran.Trans_TimeStamp;
                                    bi.BidMinBid_LastBidAmount = tran.Trans_Price;
                                    bi.BidMinBid_LastBidBy = tran.User_Id;

                                }
                                foreach (var t in trans)
                                {
                                    t.Trans_Proceesed = "1";
                                    db.Transactions.ApplyCurrentValues(t);
                                }

                                db.BidItems.ApplyCurrentValues(bi);
                                Console.WriteLine("Processed " + trans.Count() + " bids for Item " + bi.BidItemId);
                                db.SaveChanges();

                            }


                        }
                        scope.Complete();

                    }

                }

            }


        }
        catch (Exception e)
        {
            Start();
        }
    }

}

However, as both application con-currently runs, it will go into deadlock pretty fast randomly either from the first test or server application. How do i optimised my code for both side to prevent deadlocks ? I am expecting huge amount of inserts from the testspool application.

Answer 1

Since they work on the same data and get in each others way, I believe the cleanest way to do this would be to avoid executing these two at the same time.

Define a global static variable, or a mutex or a flag of some kind, maybe on the database. Whoever starts executing raises the flag, other one waits for the flag to come down. When flag comes down the other one raises the flag and starts executing.

To avoid long wait times on each class you can alter both your classes to process only a limited amount of records each turn. You should also introduce a maximum wait time for the flag. Maximum record limit should be chosen carefully to ensure that each class finishes its job in shorter time than max wait time.