Design Help - Reverse Pivot vs Running Sequential Mode

[ArjunK]

Hi,
We have a requirement where in for every column in the table we are getting a record in the input i.e. say there are 100 columns in the table , we are getting 100 records in the Input for a specific primary key column. Each record has a value of one column of the Table.

Our database is Oracle , so if we try to update these records as is in the Database , we are getting deadlock errors , since multiple records in the Input are trying to update the same record in the database.

We could think of two possible options to avoid the deadlock:

- Perform a reverse pivot in the parallel job which converts the 100 records into a single record with 100 columns for a specific primary key. Not sure what will the performance for reverse pivoting to the 100 columns.

- Run the Oracle EE stage in sequential mode.


Do you folks have a better alternate of doing this. If not which do you feel is a better option.

Thanks.

[ArndW]

If you set your commit frequency to 1 you will avoid deadlocks, won't you?

[ArjunK]

ArndW,
Even if we set the commit frequency to 1 , with Oracle EE , won't it try to do a parallel update of mutiple records and if those records are updating the same database records it can still cause the deadlock.

Isn't it possible that before one record gets committed , the other one tries to update it even with commit frequency =1?

Thanks

[ArndW]

A "deadlock" occurs when one process waits for a lock that another never releases. This won't happen when each write/update is a transaction. It might not be the most efficient of methods, but you won't be getting a record level deadlock.

[ray.wurlod]

There's nothing wrong with the vertical pivot idea. Because it's done in memory, it will probably be faster than 100 update statements.