2
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

miracle173
  • 7,797
  • 28
  • 42
Avnish Garg
  • 31
  • 1
  • 1
  • 4

2 Answers2

2

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).

captjackvane
  • 515
  • 1
  • 4
  • 9
1

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.

Avnish Garg
  • 31
  • 1
  • 1
  • 4