Question :
Select For UPDATE NOWAIT
is performed on a table by 20 processes in parallel
The error below is returned by the 4 processes out of 20 (16 processes are successful)
SQL_UPDATE_ERROR- : SQL update error ORA-02049: timeout: distributed transaction
waiting for lock ORA-02063
Are there some parameters in Oracle, which define how many connections can be made to a table using a database link?
Order of the Steps is Select
Answer :
It’s not necessarily the number of transactions, but the timeout. The parameter distrubuted_lock_timeout is set to 60 seconds by default. The purpose of this parameter is to avoid having distributed transactions in a long running wait status while something else is performing work on that row; the transaction will wait 60 seconds, then Oracle kills it.
You can modify this parameter (requires an instance restart) to whatever you want (in seconds).
Got a Solution. Increase the Value of INITRANS and Rebuild the Indexes Helped.
Table accessed via DB_LINK has INITRANS = 1, resulting in too much ITL Waits ~200 and Row Lock Waits ~100000.
Now the INITRANS Value is increased to 20, resulting in No Row Lock wait for Distributed/Concurrent Transactions.