Enqueue: Owners, Converters and Waiters

This is a case about enqueue from the book Oracle Core. The case demonstrates two feature of oracle enqueue:

1. Lock conversion
Lock conversion appears in the common foreign key problem, If supporting index is missing on a foreign key, and a session is try to update or delete on the parent table, it will lock the child table with mode 4. If the session already hold a lock on child table with mode 3, when it update or delete on the parent table, it’ll convert the lock to mode 5.

2. The priority of the converters and waiters
There are 3 queues per enqueue, owners/converters/waiters. Once the owner release the enqueue, which session will hold the lock first, the one in converters queue or the one in waiters queue?

The code to build the parent and child table, and there is supporting index on FK in child table.

oe@cs10g> drop table child;

table dropped.

oe@cs10g> drop table parent;

table dropped.

oe@cs10g> 
oe@cs10g> create table parent(id number, vc varchar2(100));

table created.

oe@cs10g> alter table parent add constraint parent_pk primary key(id)
  2  using index (create unique index parent_pk on parent(id));

table altered.

oe@cs10g> 
oe@cs10g> create table child(id number, pid number, vc varchar2(100));

table created.

oe@cs10g> 
oe@cs10g> alter table child add constraint child_pk primary key(id)
  2  using index (create unique index child_pk on child(id));

table altered.

oe@cs10g> 
oe@cs10g> alter table child add constraint child_fk
  2  foreign key(pid) references parent(id);

table altered.

oe@cs10g> 
oe@cs10g> insert into parent values(1,lpad('1',100,'0'));

1 row created.

oe@cs10g> insert into parent values(2,lpad('2',100,'0'));

1 row created.

oe@cs10g> insert into parent values(3,lpad('3',100,'0'));

1 row created.

oe@cs10g> insert into parent values(4,lpad('4',100,'0'));

1 row created.

oe@cs10g> 
oe@cs10g> commit;

commit complete.

oe@cs10g> 
oe@cs10g> insert into child values(1,1,lpad('1',100,'0'));

1 row created.

oe@cs10g> insert into child values(2,2,lpad('2',100,'0'));

1 row created.

oe@cs10g> insert into child values(3,3,lpad('3',100,'0'));

1 row created.

oe@cs10g> 
oe@cs10g> exec dbms_stats.gather_table_stats(user,'parent');

pl/sql procedure successfully completed.

oe@cs10g> exec dbms_stats.gather_table_stats(user,'child');

pl/sql procedure successfully completed.

oe@cs10g> 
oe@cs10g> select object_id from user_objects where object_name='child';

 object_id
----------
    535797

Sequence of actions is as below:
session 141, delete the only child of parent 1
session 142, delete the only child of parent 2
session 144, Attempt to lock the child table in exclusive mode (and start to wait)
session 142, Attempt to delete parent 1 (and start to wait due to missing FK index)
session 152, Attemp to delete the only child of parent 3 (and start to wait)

From query on v$lock, we can see the session 142/144/152 is blocked. Session 142 hold the lock in mode 3 together with 141. When attempt to delete the parent 1, session 142 need to convert the lock mode from 3 to 5, but session already hold the lock in mode 3, mode 3 and 5 are not comptible, so session 142 is now blocked in converters queue. We can dump the enqueue info to confirm.

--session 141
19:18:15 oe@cs10g> delete from child where pid=1;

1 row deleted.

--session 142
19:18:28 oe@cs10g> delete from child where pid=2;

1 row deleted.

--session 144
19:18:49 oe@CS10G> lock table child in exclusive mode;

--session 142
19:19:16 oe@CS10G> delete from parent where id=1;

--session 152
19:19:42 oe@CS10G> delete from child where pid=3;

--the owners/converters/waiters on the enqueue TM-53579-0
19:20:16 oe@CS10G> select sid, type, id1, id2, lmode, request, ctime, block from v$lock where type='TM' and id1=53579;

       SID TY	     ID1	ID2	 LMODE	  REQUEST      CTIME	  BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
       141 TM	   53579	  0	     3		0	 292	      1
       142 TM	   53579	  0	     3		5	 280	      1
       144 TM	   53579	  0	     0		6	 250	      0
       152 TM	   53579	  0	     0		3	 205	      0

19:28:07 oe@CS10G> alter session set events 'immediate trace name enqueues level 3';

Session altered.

--the output on the enqueue TM-535797-0
res      identification         NUL SS  SX  S   SSX X   md link
         owners              converters          waiters
-------------------------------------------------------------------------
0x18013e650 TM-0000d14b-00000000 U   0   0   2   0   0   0  8 [180153c60,180153c60]
         [17f0345d8,17f0345d8] [17f0347d8,17f0347d8] [17f0348d8,17f034ad8]
   lock     que owner    session        hold wait ser link
   ----------------------------------------------------------------------
   0x17f0345c8 OWN 0x18076ae00 0x18076ae00 (141)   SX NLCK  33 [18013e660,18013e660]
   0x17f0347c8 CON 0x18076c368 0x18076c368 (142)   SX  SSX   8 [18013e680,18013e680]
   0x17f0348c8 WAT 0x18076ee38 0x18076ee38 (144) NLCK    X  14 [17f034ad8,18013e670]
   0x17f034ac8 WAT 0x180779978 0x180779978 (152) NLCK   SX  25 [18013e670,17f0348d8]

Once sessin 141 commit the change, session 142 become the owner of the enqueue, although session 144 wait more time in the waiters queue. Because the session in the converters queues have higher priority.

19:31:37 oe@CS10G> select sid, type, id1, id2, lmode, request, ctime, block from v$lock where type='TM' and id1=53579;

       SID TY	     ID1	ID2	 LMODE	  REQUEST      CTIME	  BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
       142 TM	   53579	  0	     3		0	   3	      1
       144 TM	   53579	  0	     0		6	 763	      0
       152 TM	   53579	  0	     0		3	 718	      0

19:31:43 oe@CS10G> alter session set events 'immediate trace name enqueues level 3';

Session altered.

--the output on the enqueue TM-535797-0
res      identification         NUL SS  SX  S   SSX X   md link
         owners              converters          waiters
-------------------------------------------------------------------------
0x18013e650 TM-0000d14b-00000000 U   0   0   1   0   0   0  8 [180153c60,180153c60]
         [17f0347d8,17f0347d8] [18013e680,18013e680] [17f0348d8,17f034ad8]
   lock     que owner    session        hold wait ser link
   ----------------------------------------------------------------------
   0x17f0347c8 OWN 0x18076c368 0x18076c368 (142)   SX NLCK   8 [18013e660,18013e660]
   0x17f0348c8 WAT 0x18076ee38 0x18076ee38 (144) NLCK    X  14 [17f034ad8,18013e670]
   0x17f034ac8 WAT 0x180779978 0x180779978 (152) NLCK   SX  25 [18013e670,17f0348d8]

First come first service, when session 142 commit, session 144 with greater ctime get the enqueue. Be noted when a session hold the enqueue, the ctime will be reset to 0.

19:31:45 oe@CS10G> select sid, type, id1, id2, lmode, request, ctime, block from v$lock where type='TM' and id1=53579;

       SID TY	     ID1	ID2	 LMODE	  REQUEST      CTIME	  BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
       144 TM	   53579	  0	     6		0	   5	      1
       152 TM	   53579	  0	     0		3	 811	      0

19:33:14 oe@CS10G> alter session set events 'immediate trace name enqueues level 3';

Session altered.

--the output on the enqueue TM-535797-0
res      identification         NUL SS  SX  S   SSX X   md link
         owners              converters          waiters
-------------------------------------------------------------------------
0x18013e650 TM-0000d14b-00000000 U   0   0   0   0   0   1 40 [180153c60,180153c60]
         [17f0348d8,17f0348d8] [18013e680,18013e680] [17f034ad8,17f034ad8]
   lock     que owner    session        hold wait ser link
   ----------------------------------------------------------------------
   0x17f0348c8 OWN 0x18076ee38 0x18076ee38 (144)    X NLCK  14 [18013e660,18013e660]
   0x17f034ac8 WAT 0x180779978 0x180779978 (152) NLCK   SX  25 [18013e670,18013e670]

You may still wonder why the session can find the enqueue and attached itself to the owners/converts/waiters queue properly? Or what happen if 141 attempt to delete the parent 2, rather than than commit? Jonathan Lewis explain such detail in his book.

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>