mardi 1 septembre 2015

Deadlock on timer controlled db operation

I have 3 identical programs (made using topshelf) that run as 3 separate services for 3 different departments. When they run, they keep accessing database using timers - mainly to keep sending Alive signal or to purge the old logs. Recently I've been asked to add additional timer only for one of the departments

 Random r = new Random();   //to avoid possible deadlock we save the timestamp on random time
        int hourly =  1000 * 60 * 60;     //this is an hour



       Timer TimerItem = new Timer(TimerCallBack, null, 0, r.Next(20000, 25000));  //alive timer
       Timer purgeLogs = new Timer(TimerPurgeLogs, null,0,hourly);    //purge logs timer
       if (selDep == "planners")
       {
           Timer UpdatePlannes = new Timer(TimerUpdatePlanners, null, 0, r.Next(50000,60000));    //planners update
       }



    private static void TimerUpdatePlanners (Object o)
    {
        string conn = System.Configuration.ConfigurationManager.ConnectionStrings["Microsoft.Rtc.Collaboration.Sample.Properties.Settings.lynccallrosterConnectionString"].ToString();
        if (selDep == "planners") //Only for planners - auto turn the forwading on for everyone except the current person
        {
            string updateCommand = "update employees set on_call = 1 where Department ='Planners' and gkey <> (Select gkey from currently_on_call where Department ='Planners')";
            using (SqlConnection updatePlanners = new SqlConnection(conn))
            {
                SqlCommand executeUpdate = new SqlCommand(updateCommand, updatePlanners);
                try
                {
                    updatePlanners.Open();
                    executeUpdate.ExecuteNonQuery();
                    updatePlanners.Close();
                }
                catch { }
            }
        }

Unfortunately after I do this, it causes a hell of a problems with other program that is trying to read the same database, because it somehow deadlocks the database (SQL 2005). I can t see the reason for deadlock here: timer is executed only every 50-60s and the command execution last few millisecond, yet it seems that the connection is kept open all the time - in the activity monitor I can see dozen or so Update commands that keep clogging the data traffic. What is also interesting, this issue only happens when the program is run as a service - topsehlf allows to run service as a console app and when its run in this mode, I can't see any deadlocks or unnecessary Update commands wiating in the queue.

What have I done wrong?

Aucun commentaire:

Enregistrer un commentaire