Deadlocks in SQL when using Monitor thread insert query

Question

For some reason I am having deadlock when I try to run the SP to insert instant data to the Database

All other threads that using this table are working fine. When i manually add new row to this table through SQL server management studio it release the lock and insert all values that ware in the queue

Here is my code:

    Queue<IDbCommand> instatntDataCmdQ;

    public void ExecuteInstantDataCmd(int channel, DateTime date, double val, int status)
    {
        System.Data.SqlClient.SqlCommand cmd = GetInstantDataCmd( channel, date, val, status);

        lock (instatntDataCmdQ)
        {

            try
            {
                instatntDataCmdQ.Enqueue(cmd);
                Monitor.Pulse(instatntDataCmdQ);
            }
            catch
            {
            }
        }
    }



    void thInstantDataRun()
    {
        instatntDataCmdQ = new Queue<IDbCommand>(100);
        while (true)
        {
            try
            {
                IDbCommand cmd;
                lock (instatntDataCmdQ)
                {
                    if (instatntDataCmdQ.Count == 0)
                        Monitor.Wait(instatntDataCmdQ);
                    if (instatntDataCmdQ.Count == 0 || (cmd = instatntDataCmdQ.Dequeue()) == null)
                        break;
                }
                try
                {
                    //open connection.
                    if (cmd.Connection.State == ConnectionState.Closed)
                        cmd.Connection.Open();
                    if (cmd.Connection.State != ConnectionState.Open)
                    {
                        cmd.Connection.Close();
                        cmd.Connection.Open();
                    }


                    cmd.ExecuteNonQuery();
                }
                catch (System.Data.SqlClient.SqlException dex)
                {
                        AddDBlog(cmd.Connection, "thInstantDataRun",
                            "Critical error, duplicate dateTime&channel in InstantData table\r\n" + dex.Message);
                }
                catch (Exception err)
                {
                        AddDBlog(cmd.Connection, "thInstantDataRun", "Error Executing Command " + err.Message);
                }
                finally
                {
                    //do not close connection
                    //this some time will take up to 1 second and will make the insert of the isntant slow
                    // cmd.Connection.Close();
                    cmd.Dispose();
                    cmd = null; 
                }
            }
            catch
            {
            }
        }

    }

This piece of code is complex and verbose. Please simplify to get an answer. — usr, Sep 17 '13 at 12:17
It would be a good idea to abstract your queue using something like http://stackoverflow.com/a/530228/56778. And don't worry about "efficiency." The cost of the database update is going to swamp whatever miniscule amount of time a few virtual function calls takes. Or, even better, use something like [BlockingCollection](http://msdn.microsoft.com/en-us/library/dd267312.aspx). — Jim Mischel, Sep 17 '13 at 18:48

score 0 · Accepted Answer · answered Oct 01 '13 at 12:36

0

The problem was that i didn't close the communication between C# and SQL. The state was sign as open and was stuck. only after re-open communication the problem solved.

answered Oct 01 '13 at 12:36

YSherf

3
3

Deadlocks in SQL when using Monitor thread insert query

1 Answers1