Lock problem with PX job, 2 nodes. (DB: SQL Server 2008)

[clara.calvo]

Hello Thuong,

I'm getting a problem with a PX job, which is locking itself, and fails as deadlock victim. (Error message: Transaction (Process ID 71) was deadlocked on lock resources with another process and has been chosen as the deadlock victim)

The job it's very simple:

I've a DataSet build in 2 nodes. There are not duplicated registers in the DS.
This DS is going to update a table in SQL Server.
The job, it's locking itself (I can see it looking the processes running in DB), and it finish failing.

I suppose that this must be because of the two nodes. Two updates are done in the same time, and they lock each other. If I use the DB stage (SQLServer Enterprise stage ) in sequential, the job succeed. But then I loose the power of PX.

I'm doing the update locking each time just the updated row in the DB:
SQL instruction: update table_name WITH (ROWLOCK) set...

Why I'm getting the lock? And what can I do for solving it?
Thanks a lot for your help.

[le thuong]

Issue solved by adding an index on the column specified in the Update...where clause.