Oracle Database Locking Mechanism – 2

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.

EXAMPLES OF LOCKING CONFLICTS IN DISTRIBUTED TRANSACTIONS

In this section we will look at a number of examples where distributed locking conflicts arise. In each case we will see what would be displayed by the tools for detecting locking conflicts in each of the standalone databases involved in the distributed transaction and it will be shown how the combination of this information allows us to detect the distributed locking conflict.

The examples take place between 2 databases A and B.

In each example WA is the session that is blocked in the distributed locking conflict and is the user who calls the DBA for help. In all examples this session runs on database A.

A simple case : A non-distributed transaction being blocked by a distributed transaction locking a row in its local database. WA is blocked by a session in the same database, A. In this case, the tools (Lock Manager and utllockt.sql) do show the locking conflict.

Session BB starts a distributed transaction on database B. Through a database link from B to A it locks a row in a table on database A. This means that a session BA is started for the distributed transaction on database A and owns the lock. So, BA and BB belong to the same distributed transaction DxB.

Session WA starts up on database A. It tries to lock the same row on the table in database A as the distributed transaction DxB and is blocked because session BA owns the lock on the row.

This is the very regular task of a DBA.

Your users are complaining that some of their sessions are running very slow. You suspect that those sessions may be locked by Oracle for some reason, and would like to find the best way to go about figuring out who is holding up these sessions.

A blocking lock could “slow” a session down—in fact, the session is merely waiting on another session that is holding a lock on an object (such as a row or a set of rows, or even an entire table). Or, in a development scenario, a developer might have started multiple sessions, some of which are blocking each other.

When analyzing Oracle locks, some of the key database views you must examine are the V$LOCK and the V$SESSION views. 

The V$LOCKED_OBJECT and the DBA_OBJECTS views are also very useful in identifying the locked objects.

In Standalone:

sql>select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ') is blocking'
       || s2.username || '@' || s2.machine || '( SID=' || s2.sid || ')' from
       v$lock l1, v$session s1, v$lock l2, v$session s2
       where s1.sid=l1.sid and s2.sid=l2.sid
       and l1.block=1 and l2.request > 0
       and l1.id1=l2.id1
       and l2.id2=l2.id2;

In RAC:

sql> select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ') is blocking'
        || s2.username || '@' || s2.machine || '( SID=' || s2.sid || ')' from
       gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
       where s1.sid=l1.sid and s2.sid=l2.sid
       and l1.block=1 and l2.request > 0
       and l1.id1=l2.id1
       and l2.id2=l2.id2;

SQL> select * from V$lock where block > 0;

In an Oracle database, locking works this way:

1. A reader won’t block another reader.
2. A reader won’t block a writer.
3. A writer won’t block a reader of the same data.
4. A writer will block another writer that wants to modify the same data.