Know Oracle Lock Mode

SELECT…FROMtable... — none Y Y Y Y Y INSERTINTOtable… Yes SX Y Y N N N UPDATEtable… Yes SX Y* Y* N N N MERGEtable… Yes SX Y Y N N N DELETEtable… Yes SX Y* Y* N N N FOROF… Yes SX Y* Y* N N N LOCKTABLEIN… — ROWSHAREMODE SS Y Y Y Y N EXCLUSIVEMODE SX Y Y N N N MODE S Y N Y N N MODE SSX Y N N N N MODE X N N N N N * Yes,if no conflicting row locks are held by another transaction. Otherwise,waits occur.

mode1: NL Null N mode2: SS RS Row-S Row Share(d) SubShare Intended Share (IS) L mode3: SX RX Row-X Row Exclusive SubExclusive Intended Exclusive (IX) R mode4: S Share S mode5: SSX SRX S/Row-X Share(d) Row Exclusive Share-SubExclusive C mode6: X Exclusive X

?

compatible ?SS,RSSX,RXSSSX,SRXXSS,RSyesyesyesyesnoSX,RXyesyesnononoSyesnoyesnonoSSX,SRXyesnonononoXnonononono

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 ModeData TypeValueglobal

NUMBER

1local

NUMBER

0maxwait

NUMBER

32767nl_mode

NUMBER

1ss_modeNUMBER2sx_modeNUMBER3s_modeNUMBER4ssx_modeNUMBER5x_modeNUMBER6

Lock Compatibility Rules

When another process holds "held",an attempt to get "get" does the following
HeldNLSSSXSSSXXNLSuccessSuccessSuccessSuccessSuccessSuccessSSSuccessSuccessSuccessSuccessSuccessFailSXSuccessSuccessSuccessFailFailFailSSuccessSuccessFailFailFailFailSSXSuccessSuccessFailFailFailFailXSuccessFailFailFailFailFail 参考文献： 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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

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