Know Oracle Lock Mode

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

    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

Summary of Locks Obtained by DML Statements

SQL Statement	Row Locks	Table Lock Mode	RS	RX	S	SRX	X
SELECT ... FROM table...	—	none	Y	Y	Y	Y	Y
INSERT INTO table ...	Yes	SX	Y	Y	N	N	N
UPDATE table ...	Yes	SX	Y*	Y*	N	N	N
MERGE INTO table ...	Yes	SX	Y	Y	N	N	N
DELETE FROM table ...	Yes	SX	Y*	Y*	N	N	N
SELECT ... FROM table FOR UPDATE OF ...	Yes	SX	Y*	Y*	N	N	N
LOCK TABLE table IN ...	—
ROW SHARE MODE		SS	Y	Y	Y	Y	N
ROW EXCLUSIVE MODE		SX	Y	Y	N	N	N
SHARE MODE		S	Y	N	Y	N	N
SHARE ROW EXCLUSIVE MODE		SSX	Y	N	N	N	N
EXCLUSIVE MODE		X	N	N	N	N	N
* Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.

 

mode 1:	NL		Null				N
mode 2:	SS	RS	Row-S	Row Share(d)	SubShare	Intended Share (IS)	L
mode 3:	SX	RX	Row-X	Row Exclusive	SubExclusive	Intended Exclusive (IX)	R
mode 4:	S		Share				S
mode 5:	SSX	SRX	S/Row-X	Share(d) Row Exclusive	Share-SubExclusive		C
mode 6:	X		Exclusive				X



compatible ?	SS,RS	SX,RX	S	SSX,SRX	X
SS,RS	yes	yes	yes	yes	no
SX,RX	yes	yes	no	no	no
S	yes	no	yes	no	no
SSX, SRX	yes	no	no	no	no
X	no	no	no	no	no

GES (global enqueue resources) enqueues having different values for the lock mode:

#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)

Global Wait-For-Graph(WFG) at ddTS[0.db] :
BLOCKED 0xd876a630 5 wq 2 cvtops x1 TX 0x70015.0x81e(ext 0x2,0x0)[2B000-0001-0000057A] inst 1
BLOCKER 0xd8767a10 5 wq 1 cvtops x28 TX 0x70015.0x81e(ext 0x2,0x0)[2E000-0001-00000347] inst 1
BLOCKED 0xd876ab70 5 wq 2 cvtops x1 TX 0x40008.0x7d9(ext 0x2,0x0)[2E000-0001-00000347] inst 1
BLOCKER 0xd876a7f0 5 wq 1 cvtops x28 TX 0x40008.0x7d9(ext 0x2,0x0)[2B000-0001-0000057A] inst 1

5 means KJUSEREX ,cross instance "TX mode 6" locks