My Oracle Support Banner

Detecting and Resolving Distributed Locking Conflicts (Doc ID 118219.1)

Last updated on MAY 15, 2024

Applies to:

Oracle Database - Enterprise Edition
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

Purpose

Oracle's Distributed Database Option provides the ability to perform
transactions spanning multiple networked databases.

As in standalone databases, transactions may block each other as they
try to exclusively access database table rows, causing Locking Conflicts.
In the former case, tools and scripts exist that can help the DBA
detect and resolve such conflicts.

In distributed databases, the same tools do not always help in the detection
of such conflicts, as they operate on locking information obtained only from
the database on which they are run. This information cannot be used to detect
distributed locking conflicts unless it is combined with similar information
obtained from all databases in the distributed environment.

Such distributed locking conflicts are automatically resolved by the RDBMS
by rolling back any blocked distributed transactions that have been waiting
for a time longer than DISTRIBUTED_LOCK_TIMEOUT seconds (default 60 sec).
Unfortunately, there is no facility for allowing the blocked session to continue
after arranging for the blocker to commit/rollback/kill its session.

There are situations where it would be useful for the DBA to be able to
detect whether a session is blocked waiting on a distributed transaction
and to find the session blocking it in the network of distributed databases.
The DBA can then resolve the conflict in the usual manner e.g. by arranging
for the blocker to commit or rollback or by killing the blocking session.

This Bulletin describes a method and provides a script for performing the
above actions. When presented with a session that is assumed blocked by a
distributed locking conflict, the DBA can run the provided script and determine
the location and the identity of the session blocking the waiter. With this
information available it becomes simple for the DBA to resolve the locking
conflict by use of the same mechanisms used in standalone databases.

Troubleshooting Steps

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.