V$LOCK Missing Information

In our first BCOUG Tech Day conference, I presented my session “Look Inside the Locking Mechanism”. I presented this topics before a few times and prepared a few demos to show different locking scenarios.

During the BCOUG Tech Day I did the same, while the only difference was that for the demo I used Oracle 12.2 PDB (I think in previous times I always used 11.2). During one of the demos I noticed something strange.

This demo is about foreign keys and why it’s important to create an index on the FK column to prevent excessive locking. To show the lock levels I use v$lock, and for some reason, in this demo the blocked session was missing from v$lock.

This is the test case:

Create tables:

create table parent (id number, p_name varchar2(20));
alter table parent add constraint parent_pk primary key (id);
create table child (id number, c_name varchar2(20), p_id number);
alter table child add constraint child_fk foreign key (p_id) references parent;

insert into parent values(1, 'parent1');
insert into parent values(2, 'parent2');
insert into parent values(3, 'parent3');
insert into child values(1,'child1 of p1',1);

Reproduce the locking issue:

From session 1:

insert into child values(2,'child of p2',2);

From session 2:

delete from parent where id=3;

As you can see, session 2 is waiting, but the interesting thing is what we can see in v$lock. From a new session run this query (in my case LOCKING is the username):

select l.sid,l.type,l.id1,l.lmode,l.request,decode(l.type,'TM',o.object_name,null) object_name
from v$lock l, dba_objects o
where sid in (select sid from v$session where username='LOCKING')
 and type in ('TM','TX')
 and l.id1=o.object_id(+);

I tested it on 12.1 (no multitenant) and 12.2 (with single tenant).


       SID TY        ID1      LMODE    REQUEST OBJECT_NAME
---------- -- ---------- ---------- ---------- --------------------
        37 TM      86129          0          4 CHILD
         1 TM      86129          3          0 CHILD
        37 TM      86127          3          0 PARENT
         1 TM      86127          3          0 PARENT
         1 TX      458756         6          0

While in 12.2:

       SID TY        ID1      LMODE    REQUEST OBJECT_NAME
---------- -- ---------- ---------- ---------- --------------------
        46 TM      22647          3          0 CHILD
        46 TM      22645          3          0 PARENT
        55 TM      22645          3          0 PARENT
        46 TX      393231         6          0

Strange, the waiting on CHILD table in mode 4 is missing. The funny thing is that v$session shows that the session (55 in this case) is waiting on “enq: TM – contention” and DBA_WAITERS shows that session 55 is waiting for session 46, when 46 holds row exclusive lock on the table and 55 is waiting for share lock.

This means that there wasn’t any change in the locking mechanism, but something it wrong with the data in v$lock.


Franck Pachot saw this and investigated the issue. He found that in X$KTADM the con_id for this row is 1 (instead of the real PDB) and that’s why it doesn’t show in the PDB specific v$lock table. I will open an SR about that as it is clearly wrong information in the data dictionary.

Update (Jun-2018)

Just wanted to thank Gerald Venzl, who tested it and found out that it is fixed in 18c. Don’t know about a bug or a fix for earlier versions yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s