Oracle Scratchpad

June 21, 2010

Lock Modes

Filed under: Infrastructure,Locks — Jonathan Lewis @ 7:05 pm GMT Jun 21,2010

I usually think about locks (and the meaning of the lmode and request columns in v$lock) in terms of numbers, and never seem to remember which number goes with which name – apart from mode 6 = exclusive. So I’ve finally put up a little list somewhere I’ll always be able to find it if I need it.

Value   Name(s)                    Table method (TM lock)
    0   No lock                    n/a

    1   Null lock (NL)             Used during some parallel DML operations (e.g. update) by
                                   the pX slaves while the QC is holding an exclusive lock.

    2   Sub-share (SS)             Until 9.2.0.5/6 "select for update"
        Row-share (RS)             Since 9.2.0.1/2 used at opposite end of RI during DML
                                   Lock table in row share mode
                                   Lock table in share update mode

    3   Sub-exclusive(SX)          Update (also "select for update" from 9.2.0.5/6)
        Row-exclusive(RX)          Lock table in row exclusive mode
                                   Since 11.1 used at opposite end of RI during DML

    4   Share (S)                  Lock table in share mode
                                   Can appear during parallel DML with id2 = 1, in the PX slave sessions
                                   Common symptom of "foreign key locking" (missing index) problem
                                   Note that bitmap indexes on the child DON'T address the locking problem

    5   share sub exclusive (SSX)  Lock table in share row exclusive mode
        share row exclusive (SRX)  Less common symptom of "foreign key locking" but likely to be more
                                   frequent if the FK constraint is defined with "on delete cascade."

    6   Exclusive (X)              Lock table in exclusive mode
                                   create index    -- duration and timing depend on options used
                                   insert /*+ append */ 

(See Lock Horror for notes on the mode 3 lock during RI in 11g).

It’s a minor irritant, of course, that some of the lock modes have two names and two abbreviations – and some documents use one name and the other abbrevation (e.g. talking about row-exclusive locks, then using the SX abbreviation). The various lock trace files seem to favour SS, SX, and SSX over RS, RX and SRX.n

The change to mode 3 for “select for update” is described in Metalink (MOS) in note 3646162.8 – which references a bug of the same number describing a false deadlock issue in RAC; and it may still be possible to reverse the change in Oracle 11 (according to bug note 4969880) by setting the “_fix_control” hidden parameter to ‘4969880:ON’.

The requirement for the ‘mode 2 at opposite end of RI’ may relate to problems of checking referential integrity while executing parallel DML (unpublished bug 2435787 may apply, but I have a note to myself about parallel DML resulting in deadlocks although I can’t find anything about this on Metalink).

I also have a note about a session in 9.2 getting two TM locks on the same object during parallel dml, one in mode 1 with id1 = 0 and one in mode 4 with id1 = 1. I haven’t looked at this for a long time, though.

Update 18th Aug:

Following a thread on OTN relating to deadlocks and wait-for-graphs (WFG) from the lock manager daemon (LMD), I found the following link that showed GES (global enqueue resources) enqueues having different values for the lock mode, viz:

#define KJUSERNL 0          /* no permissions */    (Null)
#define KJUSERCR 1          /* concurrent read */   (Row-S (SS))
#define KJUSERCW 2          /* concurrent write */  (Row-X (SX))
#define KJUSERPR 3          /* protected read */    (Share)
#define KJUSERPW 4          /* protected write */   (S/Row-X (SSX))
#define KJUSEREX 5          /* exclusive access */  (Exclusive)

(You can see the “define” names appearing in gv$ges_enqueue if you want to set up a few tests in a RAC system – or you could just check the racdiag.sql script from Metalink (MOS) to see them used in a monitoring query.)

So when LMD dumps a deadlock trace with something like the following:

Global Wait-For-Graph(WFG) at ddTS[0.4] :
BLOCKED 4098bcd08 5 [0x130025][0x1475c9],[TX] [65595,583] 0
BLOCKER 412275b78 5 [0x130025][0x1475c9],[TX] [131100,2] 1
BLOCKED 41228b128 5 [0x70001][0x178a52],[TX] [131100,2] 1
BLOCKER 4098bade8 5 [0x70001][0x178a52],[TX] [65595,583] 0

you should be able to look at the column of 5’s – in this example – and assume that you have “crossed” “TX mode 6″ locks in the instances, which means you have a standard data collision except that it’s across nodes.

18 Comments »

  1. Very helpful reference.

    I cannot recall at the moment, but have you written an article that explores the interaction of a holder’s LMODE and a requester’s REQUEST mode – and the possible causes that lead to the combinations? Such an exploration might help answer the question of “What happened in the program that lead to session 1 blocking session 2?” or “Why did session 1 and session 2 deadlock?”. The exploration would be very helpful when a DBA performs a self-join on V$LOCK.

    For example:
    * TYPE=TX, LMODE=6, REQUEST=4 = session 2 is attempting to insert a row with the same primary key value or unique index value as a row just inserted by session 1
    * TYPE=TX, LMODE=6, REQUEST=4 = ITL Problem or bitmap index fragment problem
    * TYPE=TX, LMODE=6, REQUEST=6 = session 2 is attempting to update a row that is locked by session 1’s transaction (likely session 1 already updated the row)
    * TYPE=TM, LMODE=3, REQUEST=5 = possible missing index on a foreign key column

    Comment by Charles Hooper — June 22, 2010 @ 11:41 am GMT Jun 22,2010 | Reply

  2. Hi,

    Here is the way I remember them (the ‘Sub’ is explained in dbmslock.sql comments):

    S and X are Shared and eXclusive locks concerning the whole table (if talking about TM locks)
    SS/RS and SX/RX are Shared and eXclusive locks concerning only a Subset of the table (just some Rows)
    SSX/SRX is a Shared lock for whole table + eXclusive lock concerning only a Subset of the table (just some Rows)

    Then I can remember the compatibility matrix knowing that eXclusive prevents Shared or eXclusive except when this incompatibility is only at Subset level (as they can be a different subset – concerning different rows).

    Regards,
    Franck.

    Comment by Franck Pachot — June 22, 2010 @ 3:42 pm GMT Jun 22,2010 | Reply

  3. For French readers: here’s a link to a detailed article on locking by Franck Pachot. See reply to this comment for the latest location of an English translation.

    Comment by Jonathan Lewis — July 12, 2010 @ 8:16 pm GMT Jul 12,2010 | Reply

  4. […] chain (i.e. join the queue) when they attempt to create or convert a lock on an object. There are six (or seven) lock modes, and the rules are about which chain a session joins are dictated by the precedence of […]

    Pingback by Glossary « Oracle Scratchpad — August 14, 2011 @ 8:21 am GMT Aug 14,2011 | Reply

  5. […] Jonathan Lewis: Lock Modes […]

    Pingback by WFG – mode 5 TM deadlock « OraStory — November 23, 2011 @ 12:06 pm GMT Nov 23,2011 | Reply

  6. I’ve just come across an interesting case. Here’s my wait graph:

    Global Wait-For-Graph(WFG) at ddTS[0.86ce] :
    BLOCKED 0x47c45f1a08 3 wq 2 cvtops x1 TX 0x749000e.0x1b0d4(ext 0x2,0x0)[412000-0001-00002C54] inst 1 
    BLOCKER 0x47c3c30990 3 wq 1 cvtops x28 TX 0x749000e.0x1b0d4(ext 0x2,0x0)[7F2000-0001-000008C2] inst 1 
    BLOCKED 0x47c4fb3760 3 wq 2 cvtops x1 TX 0xb60011.0x1a642(ext 0x2,0x0)[7F2000-0001-000008C2] inst 1 
    BLOCKER 0x47c45f1838 3 wq 1 cvtops x28 TX 0xb60011.0x1a642(ext 0x2,0x0)[7AA000-0001-00000A15] inst 1 
    BLOCKED 0x47c3fbbca8 3 wq 2 cvtops x1 TX 0x7370009.0x190f7(ext 0x2,0x0)[7AA000-0001-00000A15] inst 1 
    BLOCKER 0x47c3589590 3 wq 1 cvtops x28 TX 0x7370009.0x190f7(ext 0x2,0x0)[412000-0001-00002C54] inst 1 
    

    Just 3s across the board. But up above, in the enqueue dumps, it is clear that all three of these enqueues are being held in KJUSEREX mode (5) and all three deadlocked transactions (holding locks in EX mode) are requesting the locks in PR mode (3). I’m not sure why the graph is displaying only 3s – maybe a bug in 11.2.0.3.0 or maybe it is only showing the modes of the requestors and not the holders?

    
    ----------resource 0x4fc68109c0----------------------
    resname       : [0x749000e][0x1b0d4],[TX][ext 0x2,0x0]
    grant_bits    : KJUSERNL KJUSEREX 
    grant mode    : KJUSERNL  KJUSERCR  KJUSERCW  KJUSERPR  KJUSERPW  KJUSEREX
    count         : 2         0         0         0         0         1
    Granted_locks : 1 
    Cvting_locks  : 2 
    
    **I'm guessing gl=granted_lock, rl=requested_lock, rp=resource_pointer?
    
    GRANTED_Q :
    lp 0x47c45f1838 gl KJUSEREX rp 0x500558c850 [0xb60011][0x1a642],[TX][ext 0x2,0x0]
      master 1 gl owner 0x47c0c31fe8 possible pid 94619 xid 7AA000-0001-00000A15 bast 0 rseq 103 mseq 0 history 0x14951495
      open opt KJUSERDEADLOCK  
    CONVERT_Q: 
    lp 0x47c3c30b60 gl KJUSERNL rl KJUSERPR rp 0x500558c850 [0xb60011][0x1a642],[TX][ext 0x2,0x0]
      master 1 gl owner 0x47c1271750 possible pid 94617 xid 79A000-0001-00000892 bast 0 rseq 103 mseq 0 history 0x49ab549a
      convert opt KJUSERGETVALUE  
    lp 0x47c3589760 gl KJUSERNL rl KJUSERPR rp 0x500558c850 [0xb60011][0x1a642],[TX][ext 0x2,0x0]
      master 1 gl owner 0x47c2005628 possible pid 94637 xid 81A000-0001-000007F2 bast 0 rseq 103 mseq 0 history 0x49ab549a
      convert opt KJUSERGETVALUE  
    
    ----------enqueue 0x47c45f1838------------------------
    grant_level      : KJUSEREX
    req_level        : KJUSEREX
    bast_level       : KJUSERNL
    pid              : 94572
    possible pid     : 94619
    xid              : 7AA000-0001-00000A15
    lock_state       : GRANTED
    Open Options     : KJUSERDEADLOCK 
    Convert options  : KJUSERNOQUEUE KJUSERNODEADLOCKWAIT 
    valblk           : 0xf0140000000000007083b5a4ff7f0000 .p
    
    
    
    

    sorry for the data dump… but I’m wondering if anyone else has seen inconsistency like this?

    also, wondering if anyone has ideas about figuring out what this enqueue is protecting? can you make any sense out of valblk perhaps?

    Comment by Jeremy Schneider — December 4, 2012 @ 12:34 am GMT Dec 4,2012 | Reply

    • >it is clear that all three of these enqueues are being held in KJUSEREX mode (5)
      Can you walk through how that is clear?

      I read this deadlock scenario like this:

      Transaction 7AA000-0001-00000A15 holds a mode 6 / X / KJUSEREX (5) lock on 0xb60011.0x1a642  
      Transaction 7AA000-0001-00000A15 wants a mode 4 / S / KJUSERPR (3) lock on 0x7370009.0x190f7  
      but is blocked by  
      Transaction 412000-0001-00002C54 holds a mode 4 / S / KJUSERPR (3) lock on 0x7370009.0x190f7  
      Transaction 412000-0001-00002C54 wants a mode 4 / S / KJUSERPR (3) lock on 0x749000e.0x1b0d4  
      but who itself is blocked by  
      Transaction 7F2000-0001-000008C2 holds a mode 4 / S / KJUSERPR (3) lock on 0x749000e.0x1b0d4  
      Transaction 7F2000-0001-000008C2 wants a mode 4 / S / KJUSERPR (3) lock on 0xb60011.0x1a642  
      who is blocked by the mode 6 of Transaction 7AA000-0001-00000A15 initially listed. 
      

      Because ASH is captured every 1 second and deadlock detection is typically 3 seconds, V$ACTIVE_SESSION_HISTORY can often provide useful additional information.

      Quoting Jonathan from this OTN thread (which was about similar lock modes but across rac nodes)

      https://forums.oracle.com/forums/thread.jspa?messageID=10659671

      “Generally tx/4 is something to do with indexes, although there are a couple of exotic causes that relate to internal activity”

      Comment by Dom Brooks — December 4, 2012 @ 10:09 am GMT Dec 4,2012 | Reply

      • Thanks Jonathan for properly formatting my code dumps. :)

        Dom – I didn’t post all three enqueue dumps to this comment, but the other two looked exactly like the one I that I did post. They also showed enqueues held in KJUSEREX. I was hoping you’d just take my word for it since copying the other enqueue dumps seemed a bit redundant!

        Even in the scenario you worked out, you showed the transaction which I posted with an X lock — which is inconsistent with the Wait-For-Graph. Any ideas about the inconsistency?

        Comment by Jeremy Schneider — December 4, 2012 @ 3:35 pm GMT Dec 4,2012 | Reply

  7. Hi Jonathan,

    So far, I knew that if parent table records are deleted, there will be a lock on child table.
    But, I recently noticed that while deleting child table records, there is share mode lock on parent
    table. Lock on parent table did not block any other DML operations, but I am curious to know why
    Oracle locks parent table while deleting child table records.

    Thanks,
    -Amit.

    /****************************************************/
    drop table child;
    drop table parent;
    create table parent
    (
        p_id  number primary key,
        p_val varchar2(30)
    );
    
    create table child
    (
        c_id  number,
        p_id  number,
        c_val varchar2(30),
        constraint fk_child foreign key (p_id) references parent(p_id)
    );
    
    create index idx_child on child(p_id);
    
    insert into parent
    select  object_id, object_name
    from    all_objects
    where   rownum <= 500
    ;
    
    insert into child
    select  o.object_id, p_id, object_name
    from    all_objects o, parent p
    where   rownum  select  *
      2  from    v$version;
    
    BANNER                                                                          
    ------------------------------------------------------------               
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi                
    PL/SQL Release 10.2.0.4.0 - Production                                          
    CORE    10.2.0.4.0    Production                                                      
    TNS for Solaris: Version 10.2.0.4.0 - Production                                
    NLSRTL Version 10.2.0.4.0 - Production                                          
    
    DBO@homedb> delete  child
      2  where   rownum  select  distinct object_name, username, sid, locked_mode
      2  from    v$locked_object l,
      3          dba_objects o,
      4          v$session s
      5  where   o.object_id = l.object_id
      6  and     l.session_id = s.sid;
    
    OBJECT_NAME   USERNAME  SID LOCKED_MODE
    ------------- ------------- -----------
    PARENT        PFSDADBO 4858           2
    CHILD         PFSDADBO 4858           3
    
    DBO@homedb> rollback;
    
    Rollback complete.
    
    DBO@homedb> alter table child disable constraint fk_child;
    
    Table altered.
    
    DBO@homedb> delete  child
      2  where   rownum  select  distinct object_name, username, sid, locked_mode
      2  from    v$locked_object l,
      3          dba_objects o,
      4          v$session s
      5  where   o.object_id = l.object_id
      6  and     l.session_id = s.sid;
    
    OBJECT_NAME USERNAME  SID LOCKED_MODE
    ------------------------- -----------
    CHILD       PFSDADBO 4858           3
    

    Comment by Amit — August 22, 2013 @ 9:59 pm GMT Aug 22,2013 | Reply

    • Amit,

      Note the comments about mode 2 (row-share/sub-share): “Since 9.2.0.1/2 used at opposite end of RI during DML”
      Then the comments about mode 3 (sub-exclusive/row-exclusive): “”Since 11.1 used at opposite end of RI during DML”

      You’re on 10.2.0.4 and reporting mode 2 – as expected.

      Comment by Jonathan Lewis — August 23, 2013 @ 8:14 pm GMT Aug 23,2013 | Reply

      • Hi Jonathan and Amit,
        Note that in 12.1.0.1 it’s a mode 2 again for that specific case (delete from child). And a mode 2 as well for an insert into parent. The other operations remain as mode 3 on opposite side.
        I’ve tested the various operations here on 12c.
        Regards,
        Franck.

        Comment by Franck Pachot — August 24, 2013 @ 10:28 am GMT Aug 24,2013 | Reply

  8. […] Lock Modes […]

    Pingback by Viewing Figures | Oracle Scratchpad — October 8, 2013 @ 5:34 pm GMT Oct 8,2013 | Reply

  9. […] article de Jonathan Lewis publié sur son blog. L’article original en anglais se trouve ici. Pour une description complète des modes de verrous, vous pouvez lire aussi: Les verrous sur les […]

    Pingback by Verrous et signification du mode (lock mode), par Jonathan Lewis | Oracle – Concepts et Exemples — November 19, 2013 @ 4:03 pm GMT Nov 19,2013 | Reply

  10. […] parent table does, too! This must be some generalization / extension of what Jonathan Lewis, in Lock modes, says about RX […]

    Pingback by Unindexed foreign keys considered harmful – but are they? | recurrent null — December 25, 2013 @ 6:05 pm GMT Dec 25,2013 | Reply


RSS feed for comments on this post. TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,432 other followers