SqlBulkCopy causes Deadlock on SQL Server 2000.

Question

I have a customized data import executable in .NET 3.5 which the SqlBulkCopy to basically do faster inserts on large amounts of data. The app basically takes an input file, massages the data and bulk uploads it into a SQL Server 2000. It was written by a consultant who was building it with a SQL 2008 database environment. Would that env difference be causing this? SQL 2000 does have the bcp utility which is what BulkCopy is based on. So, When we ran this, it triggered a Deadlock error.

Error details: Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I've tried numerous ways to try to resolve it. like temporarily setting the connection string variable MultipleActiveResultSets=true, which wasn't ideal, but it still gives a Deadlock error. I also made sure it wasn't a connection time out problem.

here's the function. Any advice?

/// <summary>
    /// Bulks the insert.
    /// </summary>
    public void BulkInsert(string destinationTableName, DataTable dataTable)
    {
        SqlBulkCopy bulkCopy;

        if (this.Transaction != null)
        {
            bulkCopy = new SqlBulkCopy
                (
                    this.Connection,
                    SqlBulkCopyOptions.TableLock,
                    this.Transaction
                );
        }
        else
        {
            bulkCopy = new SqlBulkCopy
                (
                    this.Connection.ConnectionString,
                    SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction
                );
        }

        bulkCopy.ColumnMappings.Add("FeeScheduleID", "FeeScheduleID");
        bulkCopy.ColumnMappings.Add("ProcedureID", "ProcedureID");
        bulkCopy.ColumnMappings.Add("AltCode", "AltCode");
        bulkCopy.ColumnMappings.Add("AltDescription", "AltDescription");
        bulkCopy.ColumnMappings.Add("Fee", "Fee");
        bulkCopy.ColumnMappings.Add("Discount", "Discount");
        bulkCopy.ColumnMappings.Add("Comment", "Comment");
        bulkCopy.ColumnMappings.Add("Description", "Description");


        bulkCopy.BatchSize = dataTable.Rows.Count;
        bulkCopy.DestinationTableName = destinationTableName;
        bulkCopy.WriteToServer(dataTable);

        bulkCopy = null;
    }

Answer 1

I use BCP quite regularly, and I haven't ever seen a case where the BatchSize was set to anything other than a typical value of 1000.

This field is not intended to represent the entire row count as shown in your code but to represent manageable data chunks to be sent to the server during the copy, sort of like an IP packet size.

You might try changing this value to 1000 instead of the entire table.

You might also want to look at the process/lock manager panes in the SQL Enterprise Manager or SQL Management Studio (depending on your client tool version) and see what the process is doing in terms of locks.

Answer 2

Are these inserts being done concurrently? There is a known issue with SqlBulkCopy when doing concurrent inserts with the TABLOCK hint, on a table with clustered indexes. It causes a deadlock to occur. See the following:

http://msdn.microsoft.com/en-us/library/ms186341(SQL.90).aspx

Answer 3

Multiple Active Result Sets is irrelevant for inserts - I don't even think SQL Server 2000 supports it, since it was added later.

SQL Server 2000 does not have as sophisticated a lock escalation as later versions - I expect that's what you are seeing. I assume the consultant does not have workload other than BCP on the destination table, while your application does have activity on the destination table other than the bulk insert.

I would consider doing your bulk insert into a staging table first (so no chances of deadlocks there) and then as efficient as possible an insert/update query (possibly in many small batches) in a native SQL SP.

Answer 4

I was finally able to get a local copy of our production database (~50 gigs) to test the application out. Turns out the dealocking was strictly an environment issue. Thanks fellas.