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