question

Moi avatar image
Moi asked

deadlock when bulk inserting in two different tables

Hello, I am experiencing dead-locks when performing a bulk insert on two different tables with the SqlBulkCopy classes of .net. Actually, the tool using the SqlBulkCopy class converts data from a Sybase database to an empty Sql server database. To have maximum performance, all indexes are disabled in my sql server database (except for the clustered indexes), as well as the foreign keys and the check constraints. I have used this tool successfully on environments using sql server 2008 r2, but this time I am using it on a sql server 2008 environment (build 10.0.2531). I have multiple deadlock graphs that I could show you (see my post below). In this graph, I see that the object FK_TRA_REG_UNKNOWN_A_TRA_BCI_ is locked. This is a foreign key between tables TRA_REG and TRA_BCI_ID_INFO which are both present in the deadlock graph. This foreign key is disabled, so how could it be locked? Kind greetings
sql-server-2008deadlockbulk-insert.net
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Moi avatar image
Moi answered
Hello, I looked into this with a sql server consultant, and apparently if you disable foreign keys and drop the data in the tables afterwards (which I probably did) then there could be some meta-data left in the foreign keys and such causing the locks. This would explain what I am seeing here. I performed a new test with my conversion tool starting from a brand new empty database (so without dropping existing data) and everything worked perfectly. Kind regards, Michèle Tahay
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Moi avatar image
Moi answered
insert bulk TRA_BCI_ID_INFO ([TNR_UNKNOWN] Int, [CODE_UNKNOWN] NVarChar(10) COLLATE Latin1_General_CI_AS, [DESCRIPTION_UNKNOWN] NVarChar(35) COLLATE Latin1_General_CI_AS, [NR_TYPE_ORIGINE] Decimal(1,0), [TNR_READOUT] Int) with (KEEP_NULLS, TABLOCK) insert bulk TRA_BCI_ID_INFO ([TNR_UNKNOWN] Int, [CODE_UNKNOWN] NVarChar(10) COLLATE Latin1_General_CI_AS, [DESCRIPTION_UNKNOWN] NVarChar(35) COLLATE Latin1_General_CI_AS, [NR_TYPE_ORIGINE] Decimal(1,0), [TNR_READOUT] Int) with (KEEP_NULLS, TABLOCK) insert bulk TRA_COST ([TNR_COST] Int, [CNR_COST] NVarChar(10) COLLATE Latin1_General_CI_AS, [CNR_COST_BCI] NVarChar(10) COLLATE Latin1_General_CI_AS, [NM_COST] NVarChar(35) COLLATE Latin1_General_CI_AS, [NBR_COLOR] Int) with (KEEP_NULLS, TABLOCK) insert bulk TRA_COST ([TNR_COST] Int, [CNR_COST] NVarChar(10) COLLATE Latin1_General_CI_AS, [CNR_COST_BCI] NVarChar(10) COLLATE Latin1_General_CI_AS, [NM_COST] NVarChar(35) COLLATE Latin1_General_CI_AS, [NBR_COLOR] Int) with (KEEP_NULLS, TABLOCK) insert bulk TRA_REG ([TNR_REG] Int, [TNR_PERSON] Int, [TNR_ID] Int, [BOL_COST] Decimal(1,0), [BOL_TANK] Decimal(1,0), [I_SOURCE] SmallInt, [NR_SERIAL] Int, [DT_BEGIN] DateTime, [DT_END] DateTime, [BOL_CODRIVER] Decimal(1,0), [L_DURATION] Int, [L_KMS] Int, [L_KME] Int, [TNR_TRAILER] Int, [TNR_VEHICLE] Int, [NR_SYSTEM_AN] Int, [NR_SYSTEM_VER] Int, [NR_BCI_ANS] SmallInt, [NR_BCI_VERSION] SmallInt, [C_TRAILER] NVarChar(35) COLLATE Latin1_General_CI_AS, [DT_MODIFIED] DateTime, [BOL_ORIGINAL] Decimal(1,0), [I_KIND] SmallInt, [TNR_ADDRESS] Int, [TNR_UNKNOWN_ADDRESS] Int, [TNR_CUSTOMER] Int, [TNR_UNKNOWN_CUSTOMER] Int, [TNR_SUPPLIER] Int, [TNR_UNKNOWN_SUPPLIER] Int, [NBR_QUANTITY] Decimal(10,2), [AMO_UNITY_PRICE] Decimal(12,5), [AMO_TOTAL_PRICE] Decimal(10,2), [COD_CURRENCY_CODE] NVarChar(3) COLLATE Latin1_General_CI_AS, [AMO_EXCHANGE_RATE] Decimal(8,4), [AMO_MONETARY_UNIT] Decimal(3,0), [C_FORM] NVarChar(8) COLLATE Latin1_General_CI_AS, [C_TICKET_NUMBER] NVarChar(8) COLLATE Latin1_General_CI_AS, [BOL_HIGHWAY] Decima insert bulk TRA_REG ([TNR_REG] Int, [TNR_PERSON] Int, [TNR_ID] Int, [BOL_COST] Decimal(1,0), [BOL_TANK] Decimal(1,0), [I_SOURCE] SmallInt, [NR_SERIAL] Int, [DT_BEGIN] DateTime, [DT_END] DateTime, [BOL_CODRIVER] Decimal(1,0), [L_DURATION] Int, [L_KMS] Int, [L_KME] Int, [TNR_TRAILER] Int, [TNR_VEHICLE] Int, [NR_SYSTEM_AN] Int, [NR_SYSTEM_VER] Int, [NR_BCI_ANS] SmallInt, [NR_BCI_VERSION] SmallInt, [C_TRAILER] NVarChar(35) COLLATE Latin1_General_CI_AS, [DT_MODIFIED] DateTime, [BOL_ORIGINAL] Decimal(1,0), [I_KIND] SmallInt, [TNR_ADDRESS] Int, [TNR_UNKNOWN_ADDRESS] Int, [TNR_CUSTOMER] Int, [TNR_UNKNOWN_CUSTOMER] Int, [TNR_SUPPLIER] Int, [TNR_UNKNOWN_SUPPLIER] Int, [NBR_QUANTITY] Decimal(10,2), [AMO_UNITY_PRICE] Decimal(12,5), [AMO_TOTAL_PRICE] Decimal(10,2), [COD_CURRENCY_CODE] NVarChar(3) COLLATE Latin1_General_CI_AS, [AMO_EXCHANGE_RATE] Decimal(8,4), [AMO_MONETARY_UNIT] Decimal(3,0), [C_FORM] NVarChar(8) COLLATE Latin1_General_CI_AS, [C_TICKET_NUMBER] NVarChar(8) COLLATE Latin1_General_CI_AS, [BOL_HIGHWAY] Decim
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.