Deadlocked on lock resource while selecting & updating

[Roopanwita]

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 :

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 am using ODBC connector stage both in source and Target and my update key is properly defined (not duplicate , unique).

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,

[ArndW]

In PX jobs, your SELECT and UPDATE stages are going to be different processes that will access your table, and if you have multiple nodes then there will be multiple processes for each stage as well. There is no need to use the hint "WITH (UPDLOCK)" on your update statement, as it does result in one process holding a lock when it shouldn't be.

One question - are you running multiple nodes and if yes, what is your partitioning key (hint: it should be hashed to the unique key of your table and not round-robin).

[Roopanwita]

Thank you for your reply .

I am running the job in default node (my Apt_config file has 2 nodes).

I tried to do hash partition on unique key column also , still it not working.

Thanks,

[ArndW]

Did you the remove the hint (nolock)?

[arunkumarmm]

Did you mean to say UPDLOCK?

[ArndW]

Sorry, yes; I meant to remove the explicit updlock reference.