Deadlocked on lock resource while selecting & updating
Posted: Thu Aug 22, 2013 10:32 pm
Hi,
In a job , I am selecting from a table and after few transformation , trying to update 1 field of same table (i.e. updating the same table where I am selecting records from). My database is SQLSERVER 2008 and in select query I am using WITH (NOLOCK) and in update query , I am using WITH (UPDLOCk).
My process is failing with error :
I tried to change Array Size andAuto Commit & Isolation Level options in target ODBC connector but still no luck .
I need to update the same table I am selecting records from.
Can anybody please help me .
Thank you in Advance,
In a job , I am selecting from a table and after few transformation , trying to update 1 field of same table (i.e. updating the same table where I am selecting records from). My database is SQLSERVER 2008 and in select query I am using WITH (NOLOCK) and in update query , I am using WITH (UPDLOCk).
My process is failing with error :
I am using ODBC connector stage both in source and Target and my update key is properly defined (not duplicate , unique).Transaction (Process ID #) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (CC_OdbcDBStatement::executeUpdate, file CC_OdbcDBStatement.cpp, line #)
I tried to change Array Size andAuto Commit & Isolation Level options in target ODBC connector but still no luck .
I need to update the same table I am selecting records from.
Can anybody please help me .
Thank you in Advance,