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