locking and database link

Last week-end a new release went to production. This week we got plenty of ORA-2049. What in the hell went wrong? And why did not we find out this locking before?

We just did not have that concurrency effect in test.

How does locking work ?

With regular tables:
session 1
select * from lsc_t for update;
session 2
update lsc_t set id=3;

Session 2 waits until transaction in session 1 finishes.

But in case the update is using a subquery that access a remote table, the update will fail after 1 minute

session 1
select * from lsc_t for update;
session 2
update lsc_t set x=(select * from lsc_t@db02);
Session 2 will end with ORA-02049: timeout: distributed transaction waiting for lock

The timeout of one minute is defined by distributed_lock_timeout, and it is not modifiable in your session (chances are, you will not be able to modify this parameter).

Published by

Laurent Schneider

Oracle Certified Master

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>