This Blog will try to provide a bit of author's understanding of such lock using the similar approaches listed in:
Oracle 12c single session "library cache lock (cycle)" deadlock
Oracle 11gR2 single session "library cache pin"
Setup Test by:
drop table child1;
drop table child2;
drop table parent;
create table parent as select rownum as id, cast(null as varchar2(100)) as pad from dual connect by level <= 1e3;
alter table parent add constraint parent_pk primary key (id);
create table child1 as select rownum as id, p1.id as parent_id, cast(null as varchar2(100)) as pad
from parent p1, parent p2, parent p3 where rownum <= 1e8;
alter table child1 add constraint child1_pk primary key (id);
alter table child1 add constraint child1_parent_fk foreign key (parent_id) references parent (id);
create table child2 as select rownum as id, cast(null as number) as parent_id, cast(null as varchar2(100)) as pad from dual;
alter table child2 add constraint child2_pk primary key (id);
alter table child2 add constraint child2_parent_fk foreign key (parent_id) references parent (id);
Open two Sessions and execute statements sequentially:
Session_1(SID: 304)@T1
ALTER TABLE child1 disable CONSTRAINT child1_parent_fk;
Session_1(SID: 304)@T2
ALTER TABLE child1 ENABLE CONSTRAINT child1_parent_fk;
Session_2(SID: 331)@T3
insert into child2 values (2, 2, null);
On a third Session, watch the blocking by query:
with sq as(select /*+ materialize */ sid, saddr, event
,to_char(p1, 'xxxxxxxxxxxxxxx') p1, p1text
,to_char(p2, 'xxxxxxxxxxxxxxx') p2, p2text
,to_char(p3, 'xxxxxxxxxxxxxxx') p3, p3text
from v$session where username in ('C1', 'C2') or sid in (304, 331))
select /*+ leading(s) */
(select kglnaobj||'('||kglobtyd||')'
from x$kglob v
where kglhdadr = object_handle and rownum=1) kglobj_name
,s.*, v.*
from v$libcache_locks v, sq s
where 1=1 and v.holding_user_session = s.saddr
and object_handle in (select object_handle from v$libcache_locks where mode_requested !=0)
order by kglobj_name, holding_user_session, v.type, mode_held, mode_requested;
KGLOBJ_NAME
SID
SADDR
EVENT
P1
P1TEXT
P2
P2TEXT
P3
P3TEXT
TYPE
ADDR
HOLDING_USER_SESSION
HOLDING_SESSION
OBJECT_HANDLE
LOCK_HELD
REFCOUNT
MODE_HELD
MODE_REQUESTED
SAVEPOINT_NUMBER
PARENT(TABLE)
304
000000018B731AB0
db file scattered read
6
file#
2273b0
block#
10
blocks
LOCK
000000016AF978A0
000000018B731AB0
000000018B731AB0
00000001736BBD20
0
1
3
0
5222
PARENT(TABLE)
304
000000018B731AB0
db file scattered read
6
file#
2273b0
block#
10
blocks
PIN
000000016EC0D488
000000018B731AB0
000000018B731AB0
00000001736BBD20
0
1
3
0
5222
PARENT(TABLE)
331
000000018C6EFF28
library cache lock
1736bbd20
handle address
142236f10
lock address
1069e000010002
100*mode+namespace
LOCK
0000000142236F10
000000018C6EFF28
000000018C6EFF28
00000001736BBD20
0
0
0
2
249740
where SID 304 executes at first:
ALTER TABLE child1 ENABLE CONSTRAINT child1_parent_fk;
and SID 331 executes later:
INSERT INTO child2 VALUES (2, 2, null);
SID 331 made a library cache LOCK request on PARENT(TABLE) with MODE_REQUESTED 2, however SID 304 already held a LOCK with MODE_HELD 3. Hence SID 331 was blocked, and has to wait till SID 304 released it.
In order to understand Library Cache Locks/Pins usage, a query before and after the statement will show the delta value.
For example (tested on Oracle 11.2.0.4.0), run:
alter table child2 disable constraint child2_parent_fk;
select name, loads, locks, pins, locked_total, pinned_total
from v$db_object_cache v where (name like 'PARENT%' or name like 'CHILD%') order by name;
ALTER TABLE child2 ENABLE CONSTRAINT child2_parent_fk;
select name, loads, locks, pins, locked_total, pinned_total
from v$db_object_cache v where (name like 'PARENT%' or name like 'CHILD%') order by name;
Output Before (some lines removed):
NAME LOADS LOCKS PINS LOCKED_TOTAL PINNED_TOTAL
---------- ---------- ---------- ---------- ------------ ------------
CHILD2 13 0 0 75 77
PARENT 2 0 0 60 59
Output After:
NAME LOADS LOCKS PINS LOCKED_TOTAL PINNED_TOTAL
---------- ---------- ---------- ---------- ------------ ------------
CHILD2 14 0 0 82 85
PARENT 2 0 0 66 65
LOCKED_TOTAL of CHILD2 increased 7, PARENT increased 6;
PINNED_TOTAL of PARENT increased 8, PARENT increased 6;
select name, loads, locks, pins, locked_total, pinned_total
from v$db_object_cache v where (name like 'PARENT%' or name like 'CHILD%') order by name;
-- pick a new ID to insert each time
INSERT INTO child2 VALUES (22, 22, null);
select name, loads, locks, pins, locked_total, pinned_total
from v$db_object_cache v where (name like 'PARENT%' or name like 'CHILD%') order by name;
Output Before (some lines removed):
NAME LOADS LOCKS PINS LOCKED_TOTAL PINNED_TOTAL
--------------- ---------- ---------- ---------- ------------ ------------
CHILD2 17 0 0 111 115
PARENT 2 0 0 157 156
Output After:
NAME LOADS LOCKS PINS LOCKED_TOTAL PINNED_TOTAL
--------------- ---------- ---------- ---------- ------------ ------------
CHILD2 17 0 0 112 116
PARENT 2 0 0 158 157
For insert, all LOCKED_TOTAL and PINNED_TOTAL are used exactly once.
The above Blog: Foreign Keys and Library Cache Locks attempts to find the explanation by looking on enqueue locks (OD, TM, TX and ORA-10704: Print out information about what en queues are being obtained). However, Library Cache Locks/Pins are probably about memory structures, which are evidenced by their memory address attributes.
Addendum (2016.01.21):
The Blog: Deadlock with a Virtual Column reveals one more interesting case of 'library cache lock' (cycle) deadlock, and demonstrated with the test code:
alter session set ddl_lock_timeout=37;
drop table emp;
create table emp (x number);
-- No 'library cache lock' (cycle) deadlock for varchar2
-- create table emp (x varchar2(100));
create or replace function vc (p_ename in emp.x%type)
return varchar2 deterministic is
begin
return null;
end vc;
/
alter table emp add descr as (vc (x));
truncate table emp;
ORA-04020: deadlock detected while trying to lock object KSUN.EMP
Oracle Documentation said:
ORA-04020: *Cause: While trying to lock a library object, a deadlock is detected.
During the lock (about 9 seconds), run the above query on v$libcache_locks, and we can see:
KGLOBJ_NAME
SID
SADDR
EVENT
P1
P1TEXT
P2
P2TEXT
P3
P3TEXT
TYPE
ADDR
HOLDING_USER_SESSION
HOLDING_SESSION
OBJECT_HANDLE
LOCK_HELD
REFCOUNT
MODE_HELD
MODE_REQUESTED
SAVEPOINT_NUMBER
EMP(TABLE)
304
000000018B581078
library cache lock
13ae68ea0
handle address
15d317f88
lock address
14ec9500010003
100*mode+namespace
LOCK
000000015D317F88
000000018B581078
000000018B581078
000000013AE68EA0
00
0
0
3
17'670
EMP(TABLE)
304
000000018B581078
library cache lock
13ae68ea0
handle address
15d317f88
lock address
14ec9500010003
100*mode+namespace
LOCK
0000000164949B70
000000018B581078
000000018B581078
000000013AE68EA0
000000015BE24DB0
2
2
0
17'658
EMP(TABLE)
304
000000018B581078
library cache lock
13ae68ea0
handle address
15d317f88
lock address
14ec9500010003
100*mode+namespace
PIN
000000015BE24DB0
000000018B581078
000000018B581078
000000013AE68EA0
0000000164949B70
2
2
0
17'658
where SID 304 is the test session.
A select on v$wait_chains will also returns the similar info with CHAIN_SIGNATURE: 'library cache lock' (cycle).
In order to list the objects dependencies currently maintained in the library cache (shared pool), run query:
SQL > select fobj.name from_name, tobj.to_name
from v$db_object_cache fobj, v$object_dependency tobj
where tobj.to_name in ('EMP', 'VC')
and tobj.from_hash = fobj.hash_value
order by from_name, to_name;
FROM_NAME TO_NAME
-------------------------------------------------- ----------
LOCK TABLE "EMP" IN EXCLUSIVE MODE WAIT 37 EMP
LOCK TABLE "EMP" IN EXCLUSIVE MODE WAIT 37 EMP
LOCK TABLE "EMP" IN EXCLUSIVE MODE WAIT 37 VC
LOCK TABLE "EMP" IN EXCLUSIVE MODE WAIT 37 VC
VC EMP
truncate table emp EMP
truncate table emp EMP
truncate table emp VC
truncate table emp VC
The above dependency diagram shows that "truncate table emp" depends on EMP, so it got a LOCK with MODE_HELD 2 (Share mode)
at SAVEPOINT_NUMBER 17'658. It has two direct dependency on EMP, which is indicated by REFCOUNT 2.
It has also a transitive dependency on EMP over VC, which requires a LOCK with MODE_REQUESTED 3 (Exclusive mode)
at a later SAVEPOINT_NUMBER 17'670.
