title: Transaction (Process ID 209) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim
body: <p>I have an ASP.NET web application, which get's data from a SQL Server 2008 R2 database and displays it to the users. The tables in the database gets populated with latest data from another service, every 30 minutes. This service runs for 5 mins. </p>
  
  <p>The problem I am facing is, whenever a user access the application while the tables are being updated, the following error is thrown,</p>
  
  <p>System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Data.EntityCommandExecutionException: An error occurred while reading from the store provider's data reader. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Transaction (Process ID 209) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.</p>
  
  <p>How to overcome this situation?</p>
  
  <p>The steps I follow is,</p>
  
  <pre><code>using (SqlConnection con = new SqlConnection(db.Database.Connection.ConnectionString))
                  {
                      con.Open();
                      using (SqlTransaction tran = con.BeginTransaction())
                      {       
                          string queryText = string.Format("DELETE FROM TABLE_NAME", DataDateTime);
                          SqlCommand cmd = new SqlCommand(queryText, con, tran);
                          cmd.ExecuteNonQuery(); 
  
                          SqlBulkCopy bc = new SqlBulkCopy(con,
                            SqlBulkCopyOptions.KeepNulls, tran);
  
                          bc.BatchSize = 5000;
                          bc.DestinationTableName = "TABLE_NAME";
                          DataTable data = newIncidentExtractList.ToDataTable();
                          bc.WriteToServer(data);    
  
                          string updateText = @"Update TABLE_NAME WITH CONDITION";
                          SqlCommand cmdActive = new SqlCommand(updateText, con, tran);
                          cmdActive.ExecuteNonQuery();    
  
                          string updateTextInactive = @"Update TABLE_NAME WITH CONDITION";
                          SqlCommand cmdInActive = new SqlCommand(updateTextInactive, con, tran);
                          cmdInActive.ExecuteNonQuery();    
  
                          string updateUnAssigned = @"Update TABLE_NAME WITH CONDITION";
                          SqlCommand cmdUnAssigned = new SqlCommand(updateUnAssigned, con, tran);
                          cmdUnAssigned.ExecuteNonQuery(); 
  
                          tran.Commit();
                          insertResult = true;
                      }
                      con.Close();
                  }
  </code></pre>