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

Constants

Lock Mode	Data Type	Value
global	NUMBER	1
local	NUMBER	0
maxwait	NUMBER	32767
nl_mode	NUMBER	1
ss_mode	NUMBER	2
sx_mode	NUMBER	3
s_mode	NUMBER	4
ssx_mode	NUMBER	5
x_mode	NUMBER	6

Lock Compatibility Rules

When another process holds "held", an attempt to get "get" does  the following

Held	NL	SS	SX	S	SSX	X
NL	Success	Success	Success	Success	Success	Success
SS	Success	Success	Success	Success	Success	Fail
SX	Success	Success	Success	Fail	Fail	Fail
S	Success	Success	Fail	Fail	Fail	Fail
SSX	Success	Success	Fail	Fail	Fail	Fail
X	Success	Fail	Fail	Fail	Fail	Fail

参考文献：
1. Know Oracle Lock Mode. MACLEAN LIU. 2008/06/22. http://www.askmaclean.com/archives/know-oracle-lock-mode.html 
2. Oracle USER_LOCK Version 11.1. http://psoug.org/reference/user_lock.html
3. Oracle Database Online Documentation 11g Release 2 (11.2) V$LOCK. http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_2027.htm