For some irrelevant reasons I need to delete a bunch of records by column value without saving these columns to a database. The list is huge so I run several delete operators concurrently. Recently I experienced a deadlock and I wonder what are the
possible reasons and a reliable way to prevent it? If I drop clustered index first (I drop it anyway to speed Bulk Insert up) will it help?
I have only seen this once so there're no reliable reproduction conditions to capture additional info.
So to summarize, I'm using SQL Server 2005. I have a table with clustered index and I concurrently issue a bunch of DELETE operators in form
DELETE from MyTable
where MyColumn in (Val1, Val2, ... ValN)
and the exception was
System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObjec
View Complete Post