1
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

  • Do you execute this Select For UPDATE NOWAIT via Dblink? Or is this table local one? Generally when you session touches a DB link (even for reading) your active transaction (if you have any) is labeled as distributed. Different rules are in play when you are running distributed transaction. Also distributed transaction can not start in a database being accessed via DBLink. The distributed transaction coordinator must be the "first" database - therefore you must execute some DML in your "first" database before executing Select For UPDATE NOWAIT via DBLink. Please provide more complex sample – ibre5041 Aug 22 '14 at 8:51
1

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.