Hi,
I have a job which updates and inserts a table [Update if record exists else insert]. This table has no primary keys defined on it.
This job worked fine in lower environments with fewer records (few hundreds), but failed in production with deadlock error on update statement, where the volume was 80k.
Changes made to the job now:
1. Changed target odbc stage to Hash partitioning.
2. Changed transaction and array size to 500 from 2000.
These changes caused the job to abort in lower environments with deadlock error.
Any suggestions on how to correct the design?
Note
Fatal Error:
ODBC function "SQLExecute" reported: SQLSTATE = : Native Error Code = 1205: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Legacy Driver][SQL Server]Transaction (Process ID 71) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Thanks.
update/insert deadlock
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
This is one of the issues, that we are facing on DataStage 9.1.2 (Solaris) while writing to Oracle 11.2.0.3 on three Node RAC. (APT Config = 8 Nodes). Our support provider could not replicate it in their environment and believe that this to be an Oracle database issue.
Same as Bob Oxtoby has mentioned, workaround for this issue is either run on a 1 node configuration or set the target Oracle Connector stage to process sequentially.
Another related issue, is that "Insert" of Update/Insert write mode aborts with "Unique Constraint error. Above mentioned workaround works for this issue also. Another workaround for this (for inserts to Oracle 11 target table) is to provide ignore_row_on_dupkey_index hint.
Please do raise a PMR for this issue.
Same as Bob Oxtoby has mentioned, workaround for this issue is either run on a 1 node configuration or set the target Oracle Connector stage to process sequentially.
Another related issue, is that "Insert" of Update/Insert write mode aborts with "Unique Constraint error. Above mentioned workaround works for this issue also. Another workaround for this (for inserts to Oracle 11 target table) is to provide ignore_row_on_dupkey_index hint.
Please do raise a PMR for this issue.