A distributed transaction is waiting for lock

Question

I am trying to copy all the values from a column OLD_COL into another column NEW_COL inside the same table. To achieve the result I want, I wrote down the following UPDATE in Oracle:

UPDATE MY_TABLE
   SET NEW_COL = OLD_COL
   WHERE NEW_COL IS NULL;

where MY_TABLE is a big table composed of 400.000 rows.
When I try to run it, it fails with the error:

QL Error: ORA-02049: timeout: distributed transaction waiting for lock
02049. 00000 - "timeout: distributed transaction waiting for lock"
*Cause: exceeded INIT.ORA distributed_lock_timeout seconds waiting for lock.
*Action: treat as a deadlock

I tried so to run the following query for updating one row alone:

UPDATE MY_TABLE
   SET NEW_COL = OLD_COL
   WHERE ID = '1'

and this works as intended.
Therefore, why can't I update all the rows in my table? Why is this error showing up?

Answer 1

Because there are too many row in your Table, When you UPDATE table will be lock. oracle default it set to 60 seconds. if your excute time over 60 seconds will be error.

You can try to set up timeout value

ALTER SYSTEM SET distributed_lock_timeout=120;

or disable it.

ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;

https://docs.oracle.com/cd/A84870_01/doc/server.816/a76960/ds_txnma.htm

Note:

• Remember : While running any ALTER SYSTEM Command you need to restart the instance.