As of my best knowledge, the transaction size (Commit interval) should be set to 1 for Upsert mode due to additional check is required to identify the existence of the record. So transaction size is set to 1.
Set the transaction size to 1 ??
The scenario which you mentioned may arise only if you have an Insert record and an Update record with the same key in the same input dataset.
I think if the source is expected to give an insert and an update for the same key in the same loading, the ETL design should be altered to take care of sequence of loading. IMHO.
If you're working with parallell processes it might indeed be best to set this to 1.
I've also come accross deadlock situation, that had nothing to do with bad design. Only way to avoid this is if you can partition your table and get datastage partiotioned in the same way.
Does anybody know how to set the commit interval for the 'load' - Write Method. My job log looks like it commits every 5000 rows or so, and I can't find any setting in the ctl file that Datastage generates.