Recent site activity

Wait Event Docs‎ > ‎

Oracle: Enqueues



Part I : Intro
  Lock Name(type) and Mode
  Finding waiter and blocker
  Finding Object
Part II : User  
 TM - table modification 
 TX - Transaction locks
 UL - user lock
Part III : Internal  
 CI - Cross Instance
 HW - High Water
 RO - Reusable Object
 KO
 SQ
 ST
 CF

Part I: Intro


Information we need to solve locking issues :

  1. Waiter
  2. Blocker
  3. Lock Type
    • type 
    • mode
  4. Object blocking on
  5. Blocking SQL is Missing (Possibly with log miner)

ASH data


Waiter
SESSION_ID
SESSION_SERIAL#
USER_ID
Object
CURRENT_OBJ#
CURRENT_FILE#
CURRENT_BLOCK#
SQL Waiting
SQL_ID
Blocker (as of 10gR2)
BLOCKING_SESSION
BLOCKING_SESSION_STATUS
BLOCKING_SESSION_SERIAL#
Lock Type and Mode
Event =  Type (name)
P1      =  Type | Mode

Lock Mode and Name

Select  parameter1  from v$event_name where name=‘enqueue’;

Parameter1
----------   
Name|mode

Select   p1,   p1raw   from  v$session  where  event  like  'enq%';

P1     P1RAW
---------- --------
1415053318 54580006

Type = 5458
Mode = 0006

Hex   Decimal    ASCII
54  =      84  =   "T"
58  =      88  =   "X"

Lock = TX 6

Wrapping this information  up in one query we get:

SELECT 
      chr(bitand(p1,-16777216)/16777215)||    
      chr(bitand(p1, 16711680)/65535)      Type, 
      mod(p1,16)                           lmode
from v$active_session_history 
where event like 'enq%';

TY      LMODE
-- ----------
HW          6
RO          6
JS          6

Notice that the query uses "v$active_session_history" but could also  be used on v$session starting in 10g, or v$session_wait since version 7.

9i  (and below)
One Wait : 'enqueue'
had to decode name and mode

10g 
208 enqueue waits
Specific to each type of enqueue
No longer need to decode name
but the mode can still be important to decode

For example, what was called an "enqueue" wait before 10g is now broken out like

enq: HW - contention               Configuration
enq: SQ - contention               Configuration
enq: SS - contention               Configuration
enq: ST - contention           Configuration
enq: TM - contention               Application  
enq: TW - contention                Administrative
enq: TX - allocate ITL entry        Configuration
enq: TX - index contention          Concurrency
enq: TX - row lock contention       Application
enq: TX – contention         Application


Lock Modes

 #    Type   Name
 --- -------   ---------------------------  
   1   Null    Null
   2   SS      Sub share
   3   SX      Sub exclusive
   4   S       Share
   5   SSX     Share/sub exclusive
   6   X       Exclusive

In our analysis of locks and waits we are going to talk mainly about share locks, mode 4 and exclusive locks, mode 6.
Since version 10g wait event names now have the name of the lock there is no longer a need to translate the name and we can just concentrate on translating the mode:

select
        event, 
        p1,
        mod(p1,16)  as "mode"
 from v$active_session_history
 where event like 'enq:%‘;

EVENT                                 P1 mode
----------------------------- ---------- ----
enq: TX - allocate ITL entry  1415053316    4
enq: TX - row lock contention 1415053318    6
enq: TX - row lock contention 1415053316    4



Mining ASH

          TX locks
/*
col file# for 99999
col block# for 999999
col obj for a18
col type for a9
col lm for 99
col bsid for 9999
*/

select        count(*) cnt, 
              session_id sid,
              substr(event,1,30) event, 
              mod(p1,16)  as lm,
              sql_id,
              CURRENT_OBJ# || ' ' || object_name obj
            , o.object_type type
            , CURRENT_FILE# file#
            , CURRENT_BLOCK#  block#
            , blocking_session bsid
 from v$active_session_history ash,
      all_objects o
 where
         event  like 'enq: T%'
   and o.object_id (+)= ash.current_obj#
group by event,session_id,p1,sql_ID,CURRENT_OBJ#,OBJECT_NAME,OBJECT_TYPE,CURRENT_FILE#, CURRENT_BLOCK#, BLOCKING_SESSION
order by  count(*)
/

cnt SID EVENT               LM SQL_ID        OBJ TYPE  F# BLOCK#  BSID
--- --- ------------------- -- ------------- --- ----- -- ------- ----
307 127 enq: TX - row lock   6 6yzrt1vdqrfn1 FOO TABLE  6  10541  148
307 128 enq: TX - row lock   6 6yzrt1vdqrfn1 FOO TABLE  6  10541  148
307 125 enq: TX - row lock   6 6yzrt1vdqrfn1 FOO TABLE  6  10541  148
307 126 enq: TX - row lock   6 6yzrt1vdqrfn1 FOO TABLE  6  10541  148


          TXI, like above but get index type as well, if an index is involved in the lock
/*
col file# for 99999
col block# for 999999
col obj for a18
col type for a15
*/
select    substr(event,1,30) event, sql_id,
          CURRENT_OBJ# || ' ' || object_name obj
        , o.object_type type
        , CURRENT_FILE# file#
        , CURRENT_BLOCK#  block#
 from v$active_session_history ash,
          ( select a.object_name, 
                   a.object_id,
                   decode(a.object_type,'INDEX',i.index_type||' '||'INDEX',a.object_type) object_type
            from all_objects a, all_indexes i where 
            a.owner=i.owner(+) and a.object_name=i.index_name(+) ) o
 where
         event  like 'enq: TX%'
   and o.object_id (+)= ash.current_obj#
 order by sample_time
/


Part II: User Locks

TX - Transaction Lock
Mode 6: Modifying same row
Mode 4: several reasons
TM - Table Modification
Mode 4: Unindexed Foreign Key
UL - User Lock

How a TX lock happens



TX = Transaction = Wait on UNDO 
Mode 6 (exclusive)
 modification of a row lock
Mode 4 (share)
Index block spilt
Unique Index Key enforcement
Foreign key enforcement
ITL space waits
Bitmap chunk conflicts
Alter tablespace … read only;
Free Lists slot waits
Possible with two phase commit

TX mode 6 is straight forward, just two sessions trying to change the same data

TX mode 4 has many reasons and historically has been hard to diagnose, but now in 10g Oracle had broken down TX locks in to these sub types:
  • enq: TX - row lock contention
    • pk or unique index violation
    • fk violation
    • bitmap chunk wait
    • others ?
  • enq: TX - allocate ITL entry 
    • Wait on an ITL slot    
  • enq: TX - index contention
    • Index block split    
  • enq: TX - contention
    • Wait for a  data file extension
    • Alter  tbs read only 
    • others?
(NOTE: that "enq: TX - row lock contention" can happen in both mode 6, the typical case, or mode 4 which is shown above)

enq: TX - row lock contention , mode 6


 User1      User 2
 delete from toto where id =1; 
  delete from toto where id=1;
--> wait till Use 1 commits or rollbacks

enq: TX - row lock contention , mode 4


Mode 4, happens for 3 reasons
  1. Unique key contention
  2. Foreign Key contention
  3. Bitmap index contention
  (others?)

enq: TX - row lock contention, mode 4, unique index 


 user 1 user 2
create table p (n number);
create unique index p_i on p(n);
 
 insert into p values(2); 
  insert into p values (2);

enq: TX - row lock contention, mode 4, foreign key



 user 1 user 2
create table parent (
     id number primary key);
create table child (
     id number references parent,
     name varchar2(20));
 
 insert into p values(2); 
 insert into child values (2,88);


enq: TX - row lock contention, mode 4, bitmap indexes


Bitmaps are compressed
Changes to the same bitmap cause locks

 user 1 user 2
create table t1 (
      n1 number(4),
     n2 number(4));
 insert into t1
      select 1, rownum
      from all_objects
       where rownum <= 400;
commit; create bitmap index i1
      on t1(n1);
 
 update t1 set n1 = 2 where n2 = 12; 
 update t1 set n1 = 2 where n2 = 13;


Different rows but same key value.


Three types of TX row contention mode 4 as seen from ASH data:


Unique Index
ST    EVENT                  SID  LM     P2   P3 OBJ   OTYPE  FN BLOCKN SQL_ID         BSID
----- ---------------------- --- --- ------ ---- ----- ----- --- ------ -------------- ----
10:39 enq: TX - row lock c   141   4 655406 6672 -1            0      0 bjvx94vnxtxgv   158
10:39 enq: TX - row lock c   141   4 655406 6672 -1            0      0 bjvx94vnxtxgv   158
10:39 enq: TX - row lock c   141   4 655406 6672 -1            0      0 bjvx94vnxtxgv   158
10:39 enq: TX - row lock c   141   4 655406 6672 -1            0      0 bjvx94vnxtxgv   158

Foreign Key  (10.2.0.3)
ST    EVENT                  SID  LM     P2   P3 OBJ   OTYPE  FN BLOCKN SQL_ID         BSID
----- ---------------------- --- --- ------ ---- ----- ----- --- ------ -------------- ----
10:41 enq: TX - row lock c   144   4 179681 7074 CHILD TABLE   1  60954 ahm7c9rupbz9r   1
10:41 enq: TX - row lock c   144   4 179681 7074 CHILD TABLE   1  60954 ahm7c9rupbz9r   1
10:41 enq: TX - row lock c   144   4 179681 7074 CHILD TABLE   1  60954 ahm7c9rupbz9r   1

Bitmap Index
ST    EVENT                  SID  LM     P2   P3 OBJ   OTYPE  FN BLOCKN SQL_ID         BSID
----- ---------------------- --- --- ------ ---- ----- ----- --- ------ -------------- ----
10:41 enq: TX - row lock c   143   4 966081 4598 I1    INDEX   0      0 azav296xxqcjx   144
10:41 enq: TX - row lock c   143   4 966081 4598 I1    INDEX   0      0 azav296xxqcjx   144
10:41 enq: TX - row lock c   143   4 966081 4598 I1    INDEX   0      0 azav296xxqcjx   144
10:41 enq: TX - row lock c   143   4 966081 4598 I1    INDEX   0      0 azav296xxqcjx   144


enq: TX - allocate ITL entry





create table itl (
        id number,
        data varchar2(20)
    )
    pctfree 0
    initrans 1
    ;
   insert into itl select rownum,'a' from all_objects 
                   where rownum < 2000;
   commit;

    session 1: update itl set data=data where id=1;
    session 2: update itl set data=data where id=2;
    session 3: update itl set data=data where id=3;
    session 4: update itl set data=data where id=4;
    session 5: update itl set data=data where id=5;


enq: TX - index contention

Seems to be caused by index block splits.
for some ideas on how to generate this wait to look at it more

enq: TX - contention

"enq: TX - contention" seems to be the trash bucket for all other TX contention issues and seems to be used when sessions are waiting on other sessions who are blocked some issues:
Example
Data File Extension – waiter waiting for another session to extend file
Index Block Split – waiter waiting for another session to split the block

Possibilities
  • Setting Table space read only
    • Session 1 – start transaction, don’t commit
    • Session 2 – alter tablespace read only
  • Free Lists
    • Non-ASSM
    • Sessions freeing block
    • If no txs free lists available (min 16, grow up depending on block size) , pick one and wait TX 4
  • 2PC – two phase commit
    • First does a prepare the commit
    • Any read or write access in the intervening time waits TX 4

enq: TM - contention

TX locks have a corresponding TM lock
TM locks the structure from change

LOCK     Parmeter1   Parmeter2(ID1)  Parameter3(ID2)
-------  ---------   -------------   --------------- 
enq: TM  name|mode   object #        table/partition


(1) To many truncate requests
(2) Unindexed FKs
(3) Bitmap indexes on the table (low possibility; but possibility nonetheless)
- Anup Nanda
(4) direct mode insert, or insert /*+ append */ which will take TM enqueue in exclusive mode. If the transaction doesn't commit, you could see a whole bunch of sessions stack up behind it.  - Mark Bobak


enq: UL - contention


Part III: Internal locks

.

enq: CF - contention 

see bug
7516169 - SLOW INSERT TO PARENT TABLE WHEN CHILD IS REFERENCE PARTITIONED
base bug 7147650

Mark Bobak: 
Event 10359, set to level 1, stops update of the control file with invalidation
redo is stopped.  See MetaLink Doc ID 1058851.6 for more details.  
(And yes, Steve's website also discusses it.)
.

TYPE = CF

    • CF enqueues are control file enqueues, which occur during parallel access to the control file. Actions that make it necessary to access the control file can be for example, BEGIN BACKUP, redolog archiving by an ARCH process, or a logfile switch by the LGWR process. If a CF enqueue is requested within a period of 15 minutes without success, ORA-00600 [2103] occurs, and the Oracle instance may terminate (see Note 658744)..

In "checkpoint not complete" situations (Note 79341), the LGWR process may wait for a long time for the CF enqueue. This is a follow-on problem. The "checkpoint not complete" situations that cause this problem must be corrected (Note 793113).

                    See Note 658744, which contains possible causes and solutions for CF enqueues and the ORA-00600 [2103] that are triggered by it.

CF enqueue waits may also occur during an Archiver stuck (Note 391) since in such cases the ARCH process may hold the CF enqueue for a long time. In this case, CF enqueue are only a follow-on problem; after you solve the Archiver stuck situation, the enqueue waits will also disappear

enq: CI - Cross Instance

.
ndicative of high incremental checkpointing and waiting on blocks being checkpinted - increase fast_start_mttr_target
.
.

enq: HW - contention  

.
High Water- contention on moving up the high water mark - move object to ASSM or add freelists
.
.

enq: KO - fast object checkpoint 

.
used by PQO to clear out any relavent information from the cache before doing a direct read on the objects
.
.

enq: RO - fast object reuse

.
 reusable object- clearing cache for object drop/reuse, use gtt if possible, tune DBWR
.
.

enq: SQ - contention 

.
.
.
.

enq: ST - contention 

.
.
.
.

.

Part IV: Old Methods

.
Statspack
Top 5 Timed Events                                   Avg  %Total
~~~~~~~~~~~~~~~~~~                                   wait   Call
Event                            Waits    Time (s)   (ms)   Time
-------------------------- ------------ ----------- ------ ------
Enqueue                              42         126   3000   96.5
CPU time                                          4           2.8
db file sequential read             165           1      4     .4
control file sequential read        214           0      1     .1
log file switch completion            2           0     40     .1


Statspack is INSUFFICIENT, missing
  • Who is waiting
  • Who is blocking
  • What  kind of lock (type and mode)
  • What they are blocked on (object)

Statspack 10g

5 Timed Events                                   Avg %Total
~~~~~~~~~~~~~~~~~~                              wait   Call
Event                          Waits Time (s)   (ms)   Time
----------------------------- ------ -------- ------ ------
enq: TX - row lock contention     42      126   3000   96.5
CPU time                                    4           2.8
db file sequential read          165        1      4     .4
control file sequential read     214        0      1     .1
log file switch completion         2        0     40     .1


10g better because statspack now tells lock name, still missing
  • Who is waiting
  • Who is blocking
  • What  kind of lock
  • What  they are blocked on

Other Ramblings


Lock Waits: Finding the blocking SQL on Oracle

One of my pet peeves on Oracle is the inability to find out what SQL took out a lock that another user is waiting. It's easy to find the waiting user and their SQL with v$session by looking at v$session.event where the event is an "enqueue" (v8 and v9) or "enq: TX - row lock contention" and then looking up their SQL via the v$session.sql_hash_value which joins to v$sql.hash_value for the v$sql.sql_text.

So far so good and easy.

Second step of finding the blocker is really easy starting in 10g because Oracle has a new field v$session.blocking_session which can be joined back to v$session.sid to find information on that user.

The rub is that there is no way to find the SQL text that the blocking session ran that took out the original blocking lock.

For the 2 day course I teach on Active Session History (ASH) and Oracle wait events, I wanted to show students how to actually get the blocking SQL text if they really had to.
I went as far as looking at log miner to try and get the blocking SQL text and this works sometimes and sometimes it doesn't. At that point I gave up, knowing the next step was dumping the redo logs which was more research than I felt like doing at the time.

Luckily someone has picked up the torch - Doug Burns!
On the Oaktable email list I shared my research with Doug and Doug took it even farther and posted it on his blog:

Diagnosing Locking Problems using ASH - Part 1 - try OEM 10g on Lock Problem
Diagnosing Locking Problems using ASH - Part 2 - look at raw ASH data, missing blocker SQL
Diagnosing Locking Problems using ASH - Part 3 - look at raw ASH data, find the blocker SQL
Diagnosing Locking Problems using ASH – Part 4 - misleading data from ASH
Diagnosing Locking Problems using ASH – Part 5 - logminer undependable
Diagnosing Locking Problems using ASH – Part 6 - overview of all the parts (table of contents)
Diagnosing Locking Problems using ASH/LogMiner – Part 7 - Redo log dump
Diagnosing Locking Problems using ASH/LogMiner – Part 8 - Redo log dump lacks "select for update"
Diagnosing Locking Problems using ASH/LogMiner – Part 9 - Redlo log dump - search for traces of "select for update"
Comments