about oracle lock

some links are helpful.

http://jonathanlewis.wordpress.com/2010/06/21/locks/

http://psoug.org/blogs/mohan/exploring-internal-params/detect-and-resolve-locks/

Lock Modes

Filed under: Infrastructure,Locks — Jonathan Lewis @ 7:05 pm UTC Jun 21,2010

I usually think about locks (and the meaning of the lmode and request columns in v$lock) in terms of numbers, and never seem to remember which number goes with which name – apart from mode 6 = exclusive. So I’ve finally put up a little list somewhere I’ll always be able to find it if I need it.

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 create index -- duration and timing depend on options used insert /*+ append */

1. Hi,
   Here is the way I remember them (the ‘Sub’ is explained in dbmslock.sql comments):
   S and X are Shared and eXclusive locks concerning the whole table (if talking about TM locks)
   SS/RS and SX/RX are Shared and eXclusive locks concerning only a Subset of the table (just some Rows)
   SSX/SRX is a Shared lock for whole table + eXclusive lock concerning only a Subset of the table (just some Rows)
   Then I can remember the compatibility matrix knowing that eXclusive prevents Shared or eXclusive except when this incompatibility is only at Subset level (as they can be a different subset – concerning different rows).
   Regards,
   Franck.
   
   
   
   

   
   Common types of enqueues
   JQ - Job Queue. When a job (submitted by DBMS_JOB.SUBMIT) is running, it is
   protected by a JQ enqueue (which means that only one SNP-process can run the job). 
   
   ST - Space management Transaction. The ST enqueue is need to be held when the session is
   allocating/deallocating extents. If the session gets a timeout when requesting the
   ST enqueue, "ORA-1575 timeout waiting for space management" is returned. 
   
   TM - DML (Table) enqueue - when a session wants to lock a table, a TM enqueue is requested.
        If a session deletes a row in the parent-table (DEPT) and a referential constraint
        (foreign key) is created without an index on the child-table (EMP), or if the session
         is updating the column(s) that the foreign key references to then a share lock (level 4)
        is taken on the child table. If another session tries to do changes to the child-table
        they have to wait (because they want the enqueue in row exclusive mode, and that is not
        compatible with the share mode). If an index is created on the child-table?s foreign key-column
        then no share-lock is required on the child-table. 
   
   TX - Transaction. As soon as a transaction is started a TX enqueue is needed. A transaction is
   uniquely defined by the rollback segment number.
   A session can be waiting on a TX enqueue for several reasons: 
   
   1) Another session is locking the requested row.
   2) When two sessions tries to insert the same unique key into a table (none of them has done a COMMIT),
      then the last session is waiting for the first one to COMMIT or ROLLBACK.
   3) There are no free ITL (Interested Transaction List) in the
      block header (increase INI_TRANS och PCT_FREE for the segment). 
   
   UL - User Lock. A session has taken a lock with the DBMS_LOCK.REQUEST function.
   

   Which lock modes are required for which table action?

   The following table describes what lock modes on DML enqueues are actually gotten for which table operations in a standard Oracle installation.
   

   Operation	Lock Mode	LMODE	Lock	Description
   Select	NULL	1	null
   Select for update	SS	2	sub	share
   Insert	SX	3	sub	exclusive
   Update	SX	3	sub	exclusive
   Delete	SX	3	sub	exclusive
   Lock For Update	SS	2	sub	share
   Lock Share	S	4	share
   Lock Exclusive	X	6	exclusive
   Lock Row Share	SS	2	sub	share
   Lock Row Exclusive	SX	3	sub	exclusive
   Lock Share Row Exclusive	SSX	5	share/sub	exclusive
   Alter table	X	6	exclusive
   Drop table	X	6	exclusive
   Create Index	S	4	share
   Drop Index	X	6	exclusive
   Truncate table	X	6	exclusive

   compatibility of lock modes

   The compatibility of lock modes are normally represented by following matrix:
   

   	NULL	SS	SX	S	SSX	X
   NULL	Y	Y	Y	Y	Y	Y
   SS	Y	Y	Y	Y	Y	N
   SX	Y	Y	Y	N	N	N
   S	Y	Y	N	Y	N	N
   SSX	Y	Y	N	N	N	N
   X	Y	N	N	N	N	N