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.
Lock problem with PX job, 2 nodes. (DB: SQL Server 2008)
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 1
- Joined: Tue Oct 12, 2010 2:23 am
Re: Lock problem with PX job, 2 nodes. (DB: SQL Server 2008)
Issue solved by adding an index on the column specified in the Update...where clause.
Thuong
best regards
best regards