Error
ORA-2049 timeout: distributed transaction waiting for lock
SQL> sho parameter distr
-- Default: 60 sec
e.g.
In -A- Database
select * from employees where employee_id = 100 for update;
or
update employees
set salary = 15000
where employee_id = 100;
-- Locked the row
IN -B- Database
select * from employees@a_dblink where employee_id = 100
update employees@a_dblink
set salary = 10000
where employee_id = 100;
-- Error occurred after 60 seconds -> ORA-2049
To decrease wait time:
add this into init.ora file in -A- database;
*.distributed_lock_timeout=5
Then restart the instance to perform.
To wait just one second in -B- Database:
select* from employees@a_dblink
where employee_id = 100
for update wait 1;
-- Error occurred after 1 second -> ORA-2049 if the row locked
Then try update;
update employees@a_dblink
set salary = 10000
where employee_id = 100;
commit;
Not to wait:
select* from employees@a_dblink
where employee_id = 100
for update nowait;
-- Error occurred immediately -> ORA-2049 if the row locked
Then try update;
update employees@a_dblink
set salary = 10000
where employee_id = 100;
commit;
ORA-2049 timeout: distributed transaction waiting for lock
SQL> sho parameter distr
-- Default: 60 sec
e.g.
In -A- Database
select * from employees where employee_id = 100 for update;
or
update employees
set salary = 15000
where employee_id = 100;
-- Locked the row
IN -B- Database
select * from employees@a_dblink where employee_id = 100
update employees@a_dblink
set salary = 10000
where employee_id = 100;
-- Error occurred after 60 seconds -> ORA-2049
To decrease wait time:
add this into init.ora file in -A- database;
*.distributed_lock_timeout=5
Then restart the instance to perform.
To wait just one second in -B- Database:
select* from employees@a_dblink
where employee_id = 100
for update wait 1;
-- Error occurred after 1 second -> ORA-2049 if the row locked
Then try update;
update employees@a_dblink
set salary = 10000
where employee_id = 100;
commit;
Not to wait:
select* from employees@a_dblink
where employee_id = 100
for update nowait;
-- Error occurred immediately -> ORA-2049 if the row locked
Then try update;
update employees@a_dblink
set salary = 10000
where employee_id = 100;
commit;