CODEWITHSUNDEEP
c#sql-serverdeadlock
Ali Khalili
Ali Khalili

Reputation: 1694

"deadlocked on lock | communication buffer resources" exception in C# / SQLSERVER

I have a c# program working with a SQL Server database running on different client PCs. Now, I am getting this exception which [if not catched] closes my application:

Transaction (Process ID ...) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

and the stack trace shows that the exception happens here:

at Gui.DB.sqlServerWrapper.MarkAsNonFreshSample(String barcode, Int32 devID)

Where the mentioned method is this:

        public void MarkAsNonFreshSample(string barcode, int devID)
        {
            mux.WaitOne();
            var sql = "DELETE FROM results WHERE barcode=@barcode AND devID=@devID";
            var command = new SqlCommand(sql, conn);
            command.Parameters.AddWithValue("barcode", barcode);
            command.Parameters.AddWithValue("devID", devID);
            command.ExecuteNonQuery();
            mux.ReleaseMutex();
        }

I am using the "mux" mutex to exclusive access to the sql connection "conn" because my program has multiple threads.

I am not using a complex query or a long transaction (and I guess this short query is one transaction which should not cause a deadlock. am I wrong?).

Where is the problem and how I should fix it?

Upvotes: 0

Views: 2842

Answers (1)

paparazzo
paparazzo

Reputation: 45106

Sharing a connection like that is not a good practice 

public void MarkAsNonFreshSample(string barcode, int devID)
{
    using (SqlConnection con = new SqlConnection(conString))
    {
        con.Open();
        using (SqlCommand command = con.CreateCommand())
        {
            command.CommandText = "DELETE FROM results WHERE barcode=@barcode AND devID=@devID";
            command.Parameters.AddWithValue("barcode", barcode);
            command.Parameters.AddWithValue("devID", devID);
            command.ExecuteNonQuery();
        }
    } 
}

Upvotes: 2

Related Questions