Lock problem with PX job, 2 nodes. (DB: SQL Server 2008)
Posted: Wed Mar 23, 2011 11:41 am
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.
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.