ORA-02049: Timeout: Distributed Transaction Processing wait lock Simulation
I want to simulate this error and see what the problem is.
Create a table on Oracle Database B:
Create table TEST
(
A number,
B DATE
);
Insert into test (A, B) values (2, to_date ('16-04-2015 16:38:26 ', 'dd-mm-yyyy hh24: mi: ss '));
Insert into test (A, B) values (1, to_date ('24-03-2015 16:32:36 ', 'dd-mm-yyyy hh24: mi: ss '));
Insert into test (A, B) values (2, to_date ('16-04-2015 16:35:19 ', 'dd-mm-yyyy hh24: mi: ss '));
Insert into test (A, B) values (2, to_date ('16-04-2015 16:35:19 ', 'dd-mm-yyyy hh24: mi: ss '));
Commit;
On database:
Drop database link to_ B;
Create database link to_ B
Connect to B _user identified by B _password
Using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.10.15.10) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)';
Execute update on Database B without submitting:
SQL> update test set B = sysdate where A = 2;
On database:
SQL> show parameter distributed_lock_timeout;
NAME TYPE VALUE
------------------------------------------------------
Distributed_lock_timeout integer 60
SQL> update test @ to_ B set B = sysdate where A = 2;
Update test @ to_ B set B = sysdate where A = 2
*
Row 3 has an error:
ORA-02049: Timeout: Distributed Transaction Processing wait lock
ORA-02063: followed by line (from TO_ B)
Timeout is reported after 60 s, that is, the value set by distributed_lock_timeout.
Solution: the transaction on Database B is committed as soon as possible, or the SQL (update or delete or merge) on Database B is too slow and needs to be optimized.