CODEWITHSUNDEEP
sql-serverssisoledbdeadlock
jbhbs
jbhbs

Reputation: 1

SSIS OLEDB Destination Not Using Fast Load and Deadlocking

I do not typically ask questions on here unless I am truly stuck and cannot find an answer anywhere else. My question should be rather simple and apologies if it is answered somewhere else already but I was unable to find a straight ahead answer.

I am using SSIS to insert data into a table via an OLEDB Destination component. I am simply using the Data Access Mode of "Table or view" and NOT using the "fast load" data access mode. I am having problems with deadlocks as there are 4 SSIS packages that process 1 record to insert into the same destination table. It has to be setup to have different SSIS packages and to process 1 record at a time because each is an individual transaction in the source system. I have read that if using the "fast load" data access mode that there is an option for a table lock or not. If I am NOT using the fast load option does this automatically lock the table or is there no table lock when NOT using the fast load? I am trying to get to the source of a deadlock issue when two of these SSIS packages run at the same time and did not know if having the OLEDB Destinations setup to NOT use the fast load option could be a cause of the deadlock. The reason I am not using the fast load data access mode is because this SSIS packages runs for 1 record at a time and I was having problems with triggers firing when using fast load (I know about the "FIRE_TRIGGERS" option when using fast load).

A simple answer to my question is all I am looking for but any additional help is appreciated.

Upvotes: 0

Views: 1248

Answers (1)

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

Table or view mode does not use the Tablock hint. You can view the locking that is going on during that execution of your package by looking at the dmv sys.dm_tran_locks
An insert operation is going to take some level of locking on the table - IX on the page and X on the key, perhaps.

The insert operations, themselves, are not going to cause a deadlock, each insert will just wait for each other until they all complete. The triggers, on the other hand, could cause deadlocks if they are taking additional locks on the same table, but are waiting for another insert to complete. i.e.

  1. Insert 1 starts
  2. Insert 2 starts
  3. Trigger fires from Insert 1 and is waiting on Insert 2
  4. Trigger fires from Insert 2 and is waiting on Insert 1

So look at what the triggers are doing. Do they have a good index to perform their operation or are they doing scans and escalating the locks? You might consider disabling the triggers, performing the inserts and then doing the trigger operation as a post step. In any case, to test this theory, disable the triggers and see if you get deadlocks. Then look at the execution plans to see if they can be improved with a better index.

Upvotes: 1

Related Questions