CODEWITHSUNDEEP
sql-server-2008database-deadlocks
Sreedhar
Sreedhar

Reputation: 30045

SQL Bulk Insert Vs Update - DeadLock issues

I got two process which sometimes run concurrently.

First one is Bulk insert

using (var connection = new SqlConnection(connectionString))
            {
                var bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.TableLock)
                                   {DestinationTableName = "CTRL.CTRL_DATA_ERROR_DETAIL",};
                connection.Open();
                try
                {
                    bulkCopy.WriteToServer(errorDetailsDt);
                }
                catch (Exception e)
                {
                    throw new Exception("Error Bulk writing Error Details for Data Stream ID: " + dataStreamId +
                                        " Details of Error : " + e.Message);
                }
                connection.Close();
            }

Second one is Bulk Update from Stored Procedure

--Part of code from Stored Procedure--

UPDATE [CTL].[CTRL].[CTRL_DATA_ERROR_DETAIL]
            SET [MODIFIED_CONTAINER_SEQUENCE_NUMBER] = @containerSequenceNumber
              ,[MODIFIED_DATE] = GETDATE()
              ,[CURRENT_FLAG] = 'N'
            WHERE   [DATA_ERROR_KEY] = @DataErrorKey
                AND [CURRENT_FLAG] ='Y'

First process run for bit of time (depending on incoming record load) and second process always gets deadlock victim.

Should I set SqlBulkCopyOptions.TableLock so that second process waits until the resources are released.

Upvotes: 0

Views: 1559

Answers (1)

oliwa
oliwa

Reputation: 1890

By default SqlBulkCopy doesn't take exclusive locks so while it's doing it's thing and inserting data your update process kicks off and thus causes a deadlock. To get around this you could instruct SqlBulkCopy to take an exclusive table lock as you already suggested or you can set the batch size of the bulk insert to a reasonable number.

If you can get away with it I think the table lock idea is the best option.

Upvotes: 2

Related Questions