Is there a way to set ORACLE time out on a particular table?
I started 5 instances of my program to do some processing, and the commit/rollback will only be issued after one transaction has been completed. In this case, if the transaction is big, it may take up to a few hours to process.
In the following example, I have used the following abbreviation:
UL - In the process of updating by locking the whole table
U - In the process of updating without locking the whole table
W - Waiting for update
* - Finish updating
P1 - Process 1
TABLE P1 P2 P3 P4 P5
TABLE1 * * U W W
TABLE2 UL W
In the above scenario, P1 and P2 have finished updating TABLE1, however the commit/rollback will only be issued after TABLE2 has been completed. If there are a lot of records to be updated in TABLE2 table, the rest of the processes will have to wait until P1 has completed if they were to update the same row (Update lock the whole row until a commit/rollback, am I right?).
In this case, is there a way to set the waiting time of deadlock in ORACLE, so that, P2 to P5 will not return a deadlock message when they can't perform an update.