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

asp.net c# entity-framework entity-framework-6 sql-server

Question

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.

The problem I am facing is, whenever a user access the application while the tables are being updated, the following error is thrown,

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.

How to overcome this situation?

The steps I follow is,

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();
                }
1
2
4/8/2015 7:44:06 PM

Popular Answer

The only thing you can do is to review your data migration service. Keep transactions as short as possible. Do not open transaction untill you really going to write data and close it right after writing. This may be helpful.

2
4/8/2015 6:51:44 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow