SqlException: Deadlock

I have these two exceptions generated when I try to get data from SQL database in C#:

System.Data.SqlClient.SqlException: Transaction (Process ID 97) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.

System.Data.SqlClient.SqlException: Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.

System.Data.SqlClient.SqlException: Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

this is the code:

 using (SqlConnection con = new SqlConnection(datasource))
 {
    SqlCommand cmd = new SqlCommand("Select * from MyTable Where ID='1' ", con);
    cmd.CommandTimeout = 300;
    con.Open();
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    adapter.Fill(ds);
    con.Close();
    return ds.Tables[0];
 }

These happened every time.

Any ideas on how these can be resolved?

Solution

Not that this is going to help the deadlock issue, but you should be disposing your other IDisposable objects much like you're disposing your SqlConnection as such:

    using (SqlConnection con = new SqlConnection(datasource))
    using (SqlCommand cmd = new SqlCommand("Select * from MyTable Where ID='1' ", con))
    {
        cmd.CommandTimeout = 300;
        con.Open();
        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        using (DataSet ds = new DataSet())
        {
            adapter.Fill(ds);
            return ds.Tables[0];
        }
    }

You might be able to avoid the lock with a locking hint in your query thusly:

Select * from MyTable with (nolock) Where ID='1'

I want to be clear though, you're allowing for reads of uncommitted data with this solution. It's a risk in a transactional system. Read this answer. Hope this helps.