oracle lock mode 6,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