Deadlock occurs while updating SQL Server table in datastage
Posted: 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
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