deadlock error when try to update from gridview

问题:

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

i get that error when i try to update data from datagridview how can i solve it or what is the problem with my update code and thank you ,

private void Button2_Click(object sender, EventArgs e)
    {

        using (SqlConnection con = new SqlConnection("***"))
        {
            con.Open();


            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                // INSERT command:
                using (SqlCommand com = new SqlCommand("UPDATE tabl2 SET TEL8=@TEL8 WHERE id=@id and CIVILIDD=@CIVILIDD ", con))
                {
                    com.Parameters.AddWithValue("@id", dataGridView1.Rows[i].Cells[0].Value);
                    com.Parameters.AddWithValue("@CIVILIDD", dataGridView1.Rows[i].Cells[1].Value);
                    com.Parameters.AddWithValue("@TEL8", dataGridView1.Rows[i].Cells[2].Value.ToString());

                    com.ExecuteNonQuery();
                }
            }
            MessageBox.Show("Successfully UPDATE....");
        }
    }

sql server table :

id = int
CIVILIDD = bigint
TEL8 = nvarchar (MAX)

回答1:

try this if this will work

using (SqlCommand com = new SqlCommand("UPDATE tabl2 SET TEL8=@TEL8 WHERE id=@id and CIVILIDD=@CIVILIDD ", con))
{
    com.Parameters.Add("@id", SqlDbType.Int);
    com.Parameters.Add("@CIVILIDD", SqlDbType.BigInt);
    com.Parameters.Add("@TEL8", SqlDbType.NVarChar);
    for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
    {
        com.Parameters["@id"].Value = dataGridView1.Rows[i].Cells[0].Value);
        com.Parameters["@CIVILIDD"].Value = dataGridView1.Rows[i].Cells[1].Value);
        com.Parameters["@TEL8"].Value =  dataGridView1.Rows[i].Cells[2].Value.ToString());
    }
    com.ExecuteNonQuery();
}

or

alter your db to single_user before running the update then reverting it back to multi_user

new SqlCommand("Alter Database {databaseName} SET SINGLE_USER With ROLLBACK IMMEDIATE", con);
//your update commands
new SqlCommand("Alter Database {databaseName} SET MULTI_USER With ROLLBACK IMMEDIATE", con);

also for this to occur,

• check all sql connection and command creation, if all of them are using using SqlCommand and using SqlConnection. I believe this might be the reason for it to fail as a connection and command are not yet dispose.
• maybe a very busy server, research on commandtimeout and connectiontimeout, commandtimeout setting will cause your query to wait up until the query is executed if set to 0. connectiontimeout will cause your connection attempt to wait up until it has made a connection if set to 0.