SQL Server Performance Forum – Threads Archive

deadlock on bulk inserts

Guys, we have several deadlock issues where a deadlock occurs on bulk inserts statements that are issued by more than one app server at any given time. BULK INSERT HIST_STOCK FROM ‘filename’ WITH (FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘
‘ , ORDER(LOCATION, ITEM_NUMBER, PERIOD, PERIOD_TYPE) CREATE UNIQUE CLUSTERED INDEX [BASE_CLUSTERED_INDEX] ON [dbo].[HIST_STOCK]([LOCATION], [ITEM_NUMBER], [PERIOD], [PERIOD_TYPE]) WITH FILLFACTOR = 90, PAD_INDEX ON [PRIMARY] (1) – Would the problem be with the fillfactor=90 (should this be lower?) (2) – Should we use sp_tableoption ‘HIST_STOCK’,’table lock on bulk load’,1 (3) – Should we use "TABLOCK" option when issuing bulk inserts? (4) – Change to nonclustered index? (5) – SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var } or SET LOCK_TIMEOUT timeout_period (would these help?), current lock_timeout = -1 We don’t want to drop and recreate indexes and most of the times the table is empty when it’s trying to bulk insert and the deadlock occurs. Deadlock encountered …. Printing deadlock information 2006-05-11 09:20:07.10 spid3 2006-05-11 09:20:07.10 spid3 Wait-for graph 2006-05-11 09:20:07.10 spid3 2006-05-11 09:20:07.10 spid3 Node:1 2006-05-11 09:20:07.10 spid3 KEY: 8:568493204:1 (20034a13aae0) CleanCnt:1 Mode: X Flags: 0x0 2006-05-11 09:20:07.10 spid3 Grant List 3:: 2006-05-11 09:20:07.10 spid3 Owner:0x5e025c80 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:90 ECID:0 2006-05-11 09:20:07.10 spid3 SPID: 90 ECID: 0 Statement Type: INSERT Line #: 1 2006-05-11 09:20:07.10 spid3 Input Buf: Language Event: BULK INSERT HIST_STOCK FROM ‘filename’ WITH (FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘
‘ , ORDER(LOCATION, ITEM_NUMBER, PERIOD, PERIOD_T YPE)) 2006-05-11 09:20:07.10 spid3 Requested By: 2006-05-11 09:20:07.10 spid3 ResType:LockOwner Stype:’OR’ Mode: X SPID:89 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0xBF9FF9C0) Value:0x5df5ad60 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/62834) 2006-05-11 09:20:07.10 spid3 2006-05-11 09:20:07.10 spid3 Node:2 2006-05-11 09:20:07.10 spid3 KEY: 8:568493204:1 (2203a593571c) CleanCnt:1 Mode: X Flags: 0x0 2006-05-11 09:20:07.10 spid3 Grant List 2:: 2006-05-11 09:20:07.10 spid3 Owner:0x5c7b1000 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:89 ECID:0 2006-05-11 09:20:07.10 spid3 SPID: 89 ECID: 0 Statement Type: INSERT Line #: 1 2006-05-11 09:20:07.10 spid3 Input Buf: Language Event: BULK INSERT HIST_STOCK FROM ‘filename’ WITH (FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘
‘ , ORDER(LOCATION, ITEM_NUMBER, PERIOD, PERIOD_T YPE)) 2006-05-11 09:20:07.10 spid3 Requested By: 2006-05-11 09:20:07.10 spid3 ResType:LockOwner Stype:’OR’ Mode: X SPID:90 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0xBFA0D9C0) Value:0x5ca31ca0 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/8E504) 2006-05-11 09:20:07.10 spid3 Victim Resource Owner: 2006-05-11 09:20:07.10 spid3 ResType:LockOwner Stype:’OR’ Mode: X SPID:89 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0xBF9FF9C0) Value:0x5df5ad60 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/62834) What is the best option?

Guys, would a lower fillfactor help.
If the table is set to 50% that would mean 50% more space for inserts? (but this only relates to page splits not deadlocks?) Also, if we use the TABLOCK option on bulk inserts would that help? Anybody have deadlock problems with this on?

it seems SPID 3 had competition. Yes, fillfactor will help (use DBCC REINDEX with fillfactor = 30 or 50). May the Almighty God bless us all!
www.empoweredinformation.com

Hi ya, can you check what object_name( 568493204 ) is? Is it definitely this table, or are there triggers that could be causing this on another table…? it is definitely the clustered index on this table where the deadlock occurs, dbid = 8 which I presume is the database that holds the table being bulk inserted into… How long does a single transaction take? TABLOCK would ‘fix’ it but will cause bulk inserts to queue behind each other spid 3 is the system spid which detects the deadlock, spid 89 and 90 are the two that are part of the deadlock. I’m not sure that fillfactor will help here since the deadlock has two totally different key values. Could you do a full showplan on the bulk insert statement to see how SQL is trying to do the insert? Cheers
Twan

Twan, object_name( 568493204 ) = ‘HIST_STOCK’ (table), there are no triggers in this db. Single transaction takes a couple of min (each bulk load file is on avg 250K). What do you mean by "the deadlock has two totally different key values"??? I’ll see over the next couple of weeks if this fillfactor=50 helps, but the downside of a decreasing fillfactor is an increase of storage space on the disk for that table. show plan text is below: SET SHOWPLAN_ALL ON1010NULLNULL1NULLNULLNULLNULLNULLNULLNULLNULLSETON0NULL BULK INSERT HIST_STOCK FROM ‘file’ WITH (FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘
‘ , ORDER(LOCATION, ITEM_NUMBER, PERIOD, PERIOD_TYPE))1110NULLNULL1NULLNULLNULLNULLNULLNULLNULLNULLBULKINSERT0NULL

Hi ya, Spid 89 is asking for 20034a13aae0 and spid 90 is asking for 2203a593571c which from what I remember are two different leaf nodes of the clustered index on HIST_STOCK. For each row, SQL will seek for the appropriate page in the index, lock it and then try to insert the data. If the files are not sorted then sql will be attempting to lock the index at random places, if two files happen to strike the same part of the index then there is a chance of a deadlock. How easy is it for you to sort the data file (as per the columns in the clustered index) before inserting it? In theory then SQL should try to process both files, but the second bulk insert may be forced to wait if it tries to lock a part of the index that the first has already processed. Once the first bulk insert commits then the second one can continue fill factor will help temporarily, but only because to start with there are less rows per page and so less chance of two bulk inserts updating the same page in the index. Cheers
Twan

Twan,

quote:How easy is it for you to sort the data file (as per the columns in the clustered index) before inserting it?

The file is already sorted as you can see from "ORDER(LOCATION, ITEM_NUMBER, PERIOD, PERIOD_TYPE)" the bulk insert statement.

quote:but the second bulk insert may be forced to wait if it tries to lock a part of the index that the first has already processed. Once the first bulk insert commits then the second one can continue

Is this how SQL behaves when you have the TABLOCK option specified? (we are not using it at all)

quote:TABLOCK would ‘fix’ it but will cause bulk inserts to queue behind each other

Hi ya, ok… In that case I don’t quite understand what is going on… do the files tend to be for different locations?, or just different periods? TABLOCK will lock the entire table for the duration of the import, so anything else wanting to access that table (aside from queries using NOLOCK) will wait. Cheers
Twan

Your problem is 1.) avoid deadlock and 2) if deadlock, wait until its clear.
They can be address in the SQL and application side.
for SQL 1)
a. create non clustered if possible and avoid the TABLOCK hint since the SQL will sense if it needs to acquire the table lock and thus, no other can perform insert. But because of BULK INSERT, it is most likely doing TABLE LOCK. Noncluster does not store the data in leaf node.
b. You can set the lock timeout to enable process to wait until giving up. The deadlock priority will only help you in which one to drop in case of deadlock but does not resolve the deadlock itself.
c. instead of BULK INSERT, use the INSERT/SELECT..FROM OPENDATASOURCE/OPENROWSET. Bulk insert is usually used to initialize tables/load large amount of data (corollary, you are not expecting multiple users doing DML when performing initialization/large data import).
d. set a longer timeout. if you have to use BULK INSERT, while not create a temporary table and use the INSERT from the temporary table. Again, you have to weigh and make adjustment on the design/structure based on the business requirements. for application 2)
a. if the size of the file is not big, use insert statement (or stored procedure) instead of bulk insert.
b. check if there is a bulk insert operation (or table lock flag) and wait for it to be done
before doing insert. That is, if you can wait. You can also just enable user to "SUBMIT" and let the background process do the loading later on. There are couple of options you can take but you have to adjust based on what’s your business requirements and what you can do (i.e. application is untouchable so you can forget about the adjustment on the application side). May the Almighty God bless us all!
www.empoweredinformation.com

Ok, I need to know: 1) If you have a clustered index and you specifiy TABLOCK in your bulk inserts then it really does queue the inserts and does not deadlock. I have noticed with this behaviour that the locks are KEY/X. 2)Whether EXEC sp_tableoption ‘HIST_STOCK’, ‘table lock on bulk load’, ‘1’ is global i.e. if you execute it in one connection it will we available for all connections? Do I need to use this when we are using TABLOCK instead?
I have noticed with this behaviour that the locks are TAB/BU. Thanks

]]>