Deadlock occurs while updating SQL Server table in datastage

MANASHI · Post by **MANASHI** » Thu Nov 06, 2014 3:49 am

Hi,
I am updating a SQL server table in datastage. I am getting the data from 2 different tables from target table. If the updation happens in parallel mode, I am experiencing a deadlock most of the times ("ODBC function "SQLExecute" reported: SQLSTATE = 40001: Native Error Code = 1,205: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Native Wire Protocol driver][Microsoft SQL Server]Transaction (Process ID 137) was deadlocked on lock resources with another process and has been chosen as the deadlock victim)
If I make this to execute the update in sequential mode, there are no errors but the job takes around 40 minutes to update/insert only 15000 records.
We tried a few options
1. Tried to hash partition the target table on the key. Still the deadlock happened.
2. Tried to create an index in the table and hash partitioned the table on the same keys. Tried only a few times but the deadlock did not happen.

So, please let me know if the second option will work fine and it will take less time or not.
Because the sequentian execution is taking too much time for me in production.

Please let us know. I saw there are a posts in this forum in the same subjects. But those did not help me much.

Thanks

boxtoby · Post by **boxtoby** » Thu Nov 06, 2014 7:21 am

Hello,

We had the same problem and found that partitioning on the key worked, might be worth checking the partitioning once more.

You could also try running the job in a single partition and see if that fixes it and what the response times are like.

Hope that helps!

Cheers,
Bob.

priyadarshikunal · Post by **priyadarshikunal** » Thu Nov 06, 2014 7:29 am

Partitioning on the key in datastage before ODBC connector works just fine. If your main concern is performance then create index on update keys.

MANASHI · Post by **MANASHI** » Wed Nov 12, 2014 12:15 am

Do we have any other way to fix this issue? Because in sequential mode its taking too much time to execute in production.
Please let me know.

MANASHI · Post by **MANASHI** » Wed Nov 12, 2014 12:49 am

Even after hash partitioning on the key, the deadlock still happens. So, please let me know what else can I do.

priyadarshikunal · Post by **priyadarshikunal** » Thu Nov 13, 2014 5:56 am

Make sure you hash partition based on the update keys which you mention in your where clause or a subset of that. You may be having duplicates in source so it may be trying to update same record from multiple sessions.

Did you create index on update keys?