Sql – A distributed transaction is waiting for lock

oraclesqlsql-update

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?

Best Answer

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.

Related Solutions

SQLite – UPSERT not INSERT or REPLACE

Assuming three columns in the table: ID, NAME, ROLE

BAD: This will insert or replace all columns with new values for ID=1:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

GOOD: Use SQLite On conflict clause UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.

GOOD but tedious: This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be the default (NULL).

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

Sql – alter table enable table lock; and ORA-00054

From Oracle docs:

ENABLE TABLE LOCK Specify ENABLE TABLE LOCK to enable table locks, thereby allowing DDL operations on the table. All currently executing transactions must commit or roll back before Oracle Database enables the table lock.

Note that doesn't say "All currently executing transactions that are using the table". I am not sure if this really means that there can't be any transactions outstanding in the entire instance, but that could be the case.

Try getting rid of all sessions accessing the database, particularly any that might have a transaction open against this table. If you still get the error, restarting the instance might help.

Update: Sounds like you might need some assistance from Oracle Support. But I did a search on the support website and found some notes about possible reasons for this. There could be an in-doubt distributed transaction touching the table. See if these queries return anything, and if so, you need to resolve these pending transactions:

SELECT * FROM DBA_2PC_PENDING;
SELECT * FROM DBA_2PC_NEIGHBORS;

Best Answer

Related Solutions

SQLite – UPSERT *not* INSERT or REPLACE

Sql – alter table enable table lock; and ORA-00054

Related Topic

SQLite – UPSERT not INSERT or REPLACE