how to avoid Dead lock due to multiple oledb command for same table in ssis

Question

I am using multiple OLEDBCommand transformations(~10) to update same table based on different conditions. While loading data I am facing deadlock once in a while when two updates runs parallel.

Is there anyway I can set Update statement to wait if other Update is in progress ?

Note - DFT Isolation level is set to Serializable but still facing issue.

score 0 · Answer 1 · answered Aug 15 '16 at 22:16

0

You can try setting up the property "EngineThread" to be 1.

answered Aug 15 '16 at 22:16

Dance-Henry

923
1
7
11

that's a good solution however that will allow minimum 2 threads at a time and can still result in dead lock means not a full proof solution. I tried to run couple of time and no dead locks – Sam Aug 16 '16 at 23:11
I believe if Engine Thread is set to be 1, then only 1 thread at a time will be executed. – Dance-Henry Aug 16 '16 at 23:25
Engine thread 1 not allowed I am getting error. its must be 2 minimum :( – Sam Aug 17 '16 at 16:35

how to avoid Dead lock due to multiple oledb command for same table in ssis

1 Answers1