update/insert deadlock
Posted: Tue Dec 23, 2014 6:38 am
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.
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.