Deadlock issue

Question

I am using SSIS package and loading about 4.9 million records , and it continuously failing for deadlock issue

FYI, 

1. The code is taking data from the work table with some Isnull function in it and dump to destinations table

2. The worktable is joined with 56 dimensions

3. All the dimensions have nolock

4.Some of dimensions are used 5 or 6 times with diffrent Alias inside the join statement

5. There are other ETL process before the insert run(e.g before the insert there is update statement , there is also another loop that insert to work table , all these run fine.)

What possible reason could be why it failing for deadlock ?

Answer 1

Hi Beney2009,

Try giving the query hints HASHGROUP and/ or MAXDOP in your select and insert statements. Refer http://msdn.microsoft.com/en-us/library/ms181714.aspx for more details on this.

Let me know if this does not help

Answer 2

Hi Beney,

The most obvious reason is concurent resource usage during ETL execution.

The challenge is to trace and find out exact statements, which are root of the issue.

Take a look to this article: Detecting and Ending Deadlocks. There are good tips how to capture deadlock information to errorlog or Profiler.

When statements detected you have these options:

1. Manage to them to run in sequnce

or

2. Use Deadlock preventing hints like UPDLOCK for such statements

Answer 3

Hello Beney2009

Please Select The line Precedence Constraint Editor and Select Completion Value, This procedure Finish each Step before to continue the other .... Please verify Multiple Constraint.. Select Logical OR..

Please Visit This Link : http://msdn.microsoft.com/en-us/library/ms188447.aspx

Best Regards :

Leonardo Martínez P.

---

Leonardo