Identifying lock modes

The SQL in the post "Identify blocking session for an UPDATE",
bluefrog-oracle.blogspot.com/2011/11/identify-blocking-session-for-update.html
highlighted how to identify which sessions were blocking and which were waiting.

The sample output for the SQL in the above post would be something like;

BLOCKING_STATUS
-------------------------------------------------------------------
USER1@XX\HOST ( SID=214 )  is blocking USER1@XX\HOST ( SID=26 )

The scenario, illustrated with the above output, is that a session, connected as USER1, is blocking a session, also connected as USER1.

The SQL below shows more detail in relation to the types of
locks and modes that are causing blocking for each session.

The script is meant for running on a RAC server.

col User_Session  format a10
col object_name   format a40
col sid           format 99999999
col type          format a4
col serial#       format 999999999
col held          format 9999
col request       format 99999999
col Inst_ID       format 99999
col machine       format a30

set linesize 400
set pagesize 30000

select l.*, s.machine, s.osuser
from
    (
    select  /*+ all_rows */
            l.Inst_ID
           ,s.sid           SID
           ,s.username      User_Session
           ,s.serial#       Serial#
           ,l.type          type
           ,' '             object_name
           ,lmode           held
           ,request         request
    from
            gv$lock          l
           ,gv$session       s
           ,gv$process       p
    where
            s.sid           = l.sid
    and     s.username      <> ' '
    and     s.paddr         = p.addr
    and     l.type          <> 'TM'
    and    (l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
    union
    select  /*+ all_rows */
            l.Inst_ID
           ,s.sid           SID
           ,s.Username      User_Session
           ,s.serial#       Serial#
           ,l.type          type
           ,object_name     object_name
           ,lmode           held
           ,request         request
    from
            gv$lock          l
           ,gv$session       s
           ,gv$process       p
           ,sys.dba_objects o
    where
            s.sid           = l.sid
    and     o.object_id     = l.id1
    and     l.type          = 'TM'
    and     s.username      <> ' '
    and     s.paddr         = p.addr
    union
    select  /*+ all_rows */
            l.Inst_ID
           ,s.sid SID
           ,s.Username      User_Session
           ,s.serial#       Serial#
           ,l.type          type
           ,'(Rollback='||rtrim(r.name)||')' object_name
           ,lmode           held
           ,request         request
    from
            gv$lock          l
           ,gv$session       s
           ,gv$process       p
           ,v$rollname       r
    where
            s.sid           = l.sid
    and     l.type          = 'TX'
    and     l.lmode         = 6
    and     trunc(l.id1/65536) = r.usn
    and     s.username      <> ' '
    and     s.paddr         = p.addr
   )          l
  ,v$session  s
where
          s.sid         = l.sid
order by l.Object_Name, l.Held
/

There are only two types of general locks, shared and exclusive.

A many to one relationship can exist between several shared locks and
a single resource, whereas only a one to one scenario can exist
between an exclusive lock and a single resource.

Oracle manages locks on resources through enqueues.
Enqueues as shared memory structures that serialize access to database
resources.

A database resource, in relation to data locks, can be an object structure
or a transaction.

Oracle has two types of data locks:

TX = Row Locks - Finest grain of locking possible.
Oracle stores locking information of a locked row
within the data block the row resides in.

A row lock automatically generates a table lock (TM), to prevent
DDL from occurring on the table while the transaction is in progress.

TM = Table locks - Acquired by a transaction (INSERT,UPDATE,DELETE,MERGE,LOCK TABLE or SELECT FOR UPDATE).

Table locks can be one of six modes, namely;

0 = none
1 = null 
2 = row-S (RS or SS)     
Row share lock or sub-share lock. 
Transaction holds locked rows and an update is pending.
Acquired when issuing either;
  SELECT FOR UPDATE;
  LOCK TABLE IN ROW SHARE mode;

Least restrictive, since other transactions can query, insert update or delete concurrently on the same table on rows other than the ones locked with SELECT for UPDATE.
Other transactions can therefore obtain RS, RX or SRX locks.
Prevents an exlusive table (X) lock however.

3 = row-X (RX or SX)
Row exclusive lock. Transaction holding locked rows has completed the update(s) to the table.
Acquired automatically with one of the following four statements;
  INSERT;
  UPDATE;
  DELETE;
  LOCK TABLE IN ROW EXCLUSIVE MODE;

More restrictive than row share lock, since other transactions cannot concurrently
lock the table in share mode (S), share exclusive mode (SRX) using one of the following statements;
  LOCK TABLE IN SHARE MODE
  LOCK TABLE IN EXCLUSIVE MODE
in addition to not permitting any other types of exclusive (X) locks.
RX is the default locking behavior for Oracle.

4 = share (S)
Share Table. Acquired when explicitly issuing the LOCK table in SHARE MODE statement.
Only allows other transactions to query the table in share mode (S).
Prevents other transactions from modifying the same table in SRX, RX and X modes.
Transactions can hold share (S) table locks concurrently, but a SELECT for UPDATE update can only occur if there are no other transactions in share mode (S) as well.

5 = S/Row-X (SRX or SSX) 
Share Row Exclusive. Acquired explicitly when issuing a LOCK TABLE in SHARE ROW EXCLUSIVE mode. Only a single transaction at a time can acquire a share row exclusive lock.
Other transactions can however query the table, but they cannot update the table.
Prevents all types of locks from other transactions. 
Permits SELECT FOR UPDATE locks within the same transaction after issuing the LOCK TABLE statement.

6 = Exclusive (X)        
Most restrictive. Prevents any DML on the table. Acquired with
LOCK TABLE IN EXCLUSIVE MODE. Only a single transaction can obtain an exclusive lock at a time. Querying can still occur though.


This is a sample output from the above query and one can clearly

INST_ID  SID USER_SESSI SERIAL# TYPE OBJECT_NAME HELD REQUEST
------- ---- ---------- ------- ---- ----------- ---- -------
      1  222 USER2        34254 TX                  0       6
      1  221 USER1         8366 TM   TEST_LOCK      3       0


The REQUEST column will indicate the mode of lock on a Resource
that is sought, which is currently held by another
session (indicated by HELD).

A Resource can be a table definition, a transaction or any structure
that is shareable. The allowable range of values will be the same as
per the HELD column.

Oracle uses enqueues as a locking mechanisms for queuing access to
resources.

Lock conversion occurs when a lock with a lower restrictiveness is converted
to one of higher restrictiveness. For example, if a SELECT for UPDATE
is issued, the initial locks on the table would be row share (RS).

When eventually the UPDATE WHERE CURRENT OF occurs, the RS lock will be converted
to a RX lock.