deadlock while using Change capture in update

[surionline]

We are using Change data capture + transformer(using constraints to split based on change code) to implement insert/update logic. But in the update link, if a parallel odbc connector stage is used, transaction deadlock message is displayed in the log and job aborts with partial data update.eg:- out of the 10 records to be updated, only 2 will get updated.
If a sequential odbc enteprise stage or sequential drs stage is used, job gets finished with all the records getting updated correctly. The job is running on 4 nodes.
Please advise why the transaction deadlock is happening when the parallel odbc connector stage is used along with change capture.

[ArndW]

I would look at how you are reading your data - each database has different syntax for ensuring that you select without taking locks. In addition, you need to ensure that no 2 nodes can update the same records - the easiest solution here is to partition on the database key. Another easy, but inefficient, method is to reduce your commit size to 1.

[surionline]

I would look at how you are reading your data - each database has different syntax for ensuring that you select without taking locks. In addition, you need to ensure that no 2 nodes can update the same records - the easiest solution here is to partition on the database key. Another easy, but inefficient, method is to reduce your commit size to 1.

-Database is SQL Server, we tried with NO LOCK while reading, no luck
-no duplicate key records going in the update link, so no 2 nodes can update the same records
-tried with hash partition, no luck
-tried with transaction = 1, no luck

Is there any database or datastage project settings to be changed?

[sajidkp]

Try changing property of target stage (ODBC) to Sequentail from Parellel. Ther can be performance reducion