TX Transaction locks - Example wait scenarios

    文章不错，有利于对事务锁的理解。

Introduction
~~~~~~~~~~~~
  This short article gives examples of TX locks and the waits which can
  occur in various circumstances. Often such waits will go unnoticed unless
  they are of a long duration or when they trigger a deadlock scenario (which
  raises an ORA-60 error).

  The examples here demonstrate fundamental locking scenarios which should
  be understood by application developers and DBA's alike.


Useful SQL statements
~~~~~~~~~~~~~~~~~~~~~
  If you encounter a lock related hang scenario the following SQL statements
  can be used to help isolate the waiters and blockers:

    Show all sessions waiting for any lock:

select event,p1,p2,p3 from v$session_wait
where wait_time=0 and event='enqueue';

    Show sessions waiting for a TX lock:

select * from v$lock where type='TX' and request>0;

    Show sessions holding a TX lock:

select * from v$lock where type='TX' and lmode>0;


What is a TX lock ?
~~~~~~~~~~~~~~~~~~~
  A TX lock is acquired when a transaction initiates its first change and is
  held until the transaction does a COMMIT or ROLLBACK. It is used mainly as
  a queuing mechanism so that other sessions can wait for the transaction to
  complete. The lock name (ID1 and ID2) of the TX lock reflect the transaction
  ID of the active transaction.


Example Tables
~~~~~~~~~~~~~~
  The lock waits which can occur are demonstrated using the following
  tables. Connect as SCOTT/TIGER or some dummy user to set up the test
  environment using the following SQL:

    DROP TABLE tx_eg;
    CREATE TABLE tx_eg ( num number, txt varchar2(10), sex varchar2(10) )
      INITRANS 1 MAXTRANS 1;
    INSERT into tx_eg VALUES ( 1, 'First','FEMALE' );
    INSERT into tx_eg VALUES ( 2, 'Second','MALE' );
    INSERT into tx_eg VALUES ( 3, 'Third','MALE' );
    INSERT into tx_eg VALUES ( 4, 'Fourth','MALE' );
    INSERT into tx_eg VALUES ( 5, 'Fifth','MALE' );
    COMMIT;

  In the examples below three sessions are required:

Ses#1 indicates the TX_EG table owners first session
Ses#2 indicates the TX_EG table owners second session
DBA indicates a SYSDBA user with access to


  The examples covered below include:

Waits due to Row being locked by an active Transaction
Waits due to Unique or Primary Key Constraint enforcement
Waits due to Insufficient 'ITL' slots in the Block
Waits due to rows being covered by the same BITMAP index fragment


Waits due to Row being locked by an active Transaction
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  When a session updates a row in a table the row is locked by the sessions
  transaction. Other users may SELECT that row and will see row as it was
  BEFORE the UPDATE occurred. If another session wishes to UPDATE the same
  row it has to wait for the first session to commit or rollback. The
  second session waits for the first sessions TX lock in EXCLUSIVE mode.

  Eg:
Ses#1: update tx_eg set txt='Garbage' where num=1;
Ses#2: update tx_eg set txt='Garbage' where num=1;
DBA: select SID,TYPE,ID1,ID2,LMODE,REQUEST
from v$lock where type='TX';

SID        TY ID1        ID2        LMODE      REQUEST
---------- -- ---------- ---------- ---------- ----------
         8 TX     131075        597          6          0
        10 TX     131075        597          0          6

> This shows SID 10 is waiting for the TX lock held by SID 8 and it
> wants the lock in exclusive mode (as REQUEST=6).

The select below is included to demonstrate that a session waiting
on a lock will show as waiting on an 'enqueue' in V$SESSION_WAIT
and that the values of P1RAW, P2 and P3 indicate the actual lock
being waited for. When using Parallel Server the EVENT will be
'DFS enqueue lock acquisition' rather than 'enqueue'.
This select will be omitted from the following examples.

DBA: select sid,p1raw, p2, p3
  from v$session_wait
where wait_time=0 and event='enqueue';

SID        P1RAW    P2         P3
---------- -------- ---------- ----------
        10 54580006     131075        597
>    ~~~~  ~~ ~~~~~~       ~~~
>    type|mode       id1       id2
>     T X   6 131075       597


The next select shows the object_id and the exact row that the
session is waiting for. This information is only valid in V$SESSION
when a session is waiting due to a row level lock. The statement
is only valid in Oracle 7.3 onwards. As SID 10 is the waiter above
  then this is the session to look at in V$SESSION:

DBA: select ROW_WAIT_OBJ#,
       ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
     from v$session
     where sid=10;

ROW_WAIT_O ROW_WAIT_F ROW_WAIT_B ROW_WAIT_R
---------- ---------- ---------- ----------
      3058          4       2683          0

> The waiter is waiting for the TX lock in order to lock row 0
> in file 4, block 2683 of object 3058.

Ses#1: rollback;
Ses#2: rollback;


Waits due to Unique or Primary Key Constraint enforcement
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  If a table has a primary key constraint, a unique constraint
  or a unique index then the uniqueness of the column/s referenced by
  the constraint is enforced by a unique index. If two sessions try to
  insert the same key value the second session has to wait to see if an
  ORA-0001 should be raised or not.

  Eg:
Ses#1:  ALTER TABLE tx_eg ADD CONSTRAINT tx_eg_pk PRIMARY KEY( num );
Ses#1: insert into tx_eg values (10,'New','MALE');
Ses#2: insert into tx_eg values (10,'OtherNew',null);
        DBA:    select SID,TYPE,ID1,ID2,LMODE,REQUEST
                 from v$lock where type='TX';

SID        TY ID1        ID2        LMODE      REQUEST
---------- -- ---------- ---------- ---------- ----------
         8 TX     196625         39          6          0
        10 TX     262155         65          6          0
        10 TX     196625         39          0          4

This shows SID 10 is waiting for the TX lock held by SID 8 and it
wants the lock in share mode (as REQUEST=4). SID 10 holds a TX lock
for its own transaction.

Ses#1: commit;
Ses#2:  ORA-00001: unique constraint (SCOTT.TX_EG_PK) violated
Ses#2: rollback;


Waits due to Insufficient 'ITL' slots in a Block
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Oracle keeps note of which rows are locked by which transaction in an area
  at the top of each data block known as the 'interested transaction list'.
  The number of ITL slots in any block in an object is controlled by
  the INITRANS and MAXTRANS attributes. INITRANS is the number of slots
  initially created in a block when it is first used, while MAXTRANS places
  an upper bound on the number of entries allowed. Each transaction which
  wants to modify a block requires a slot in this 'ITL' list in the block.

  MAXTRANS places an upper bound on the number of concurrent transactions
  which can be active at any single point in time within a block.

  INITRANS provides a minimum guaranteed 'per-block' concurrency.

  If more than INITRANS but less than MAXTRANS transactions want to be
  active concurrently within the same block then the ITL list will be extended
  BUT ONLY IF THERE IS SPACE AVAILABLE TO DO SO WITHIN THE BLOCK.

  If there is no free 'ITL' then the requesting session will wait on one
  of the active transaction locks in mode 4.

  Eg:   Ses#1:  update tx_eg set txt='Garbage' where num=1;
        Ses#2:  update tx_eg set txt='Different' where num=2;
        DBA:    select SID,TYPE,ID1,ID2,LMODE,REQUEST
                 from v$lock where type='TX';

SID        TY ID1        ID2        LMODE      REQUEST
---------- -- ---------- ---------- ---------- ----------
         8 TX     327688         48          6          0
        10 TX     327688         48          0          4

This shows SID 10 is waiting for the TX lock held by SID 8 and it
wants the lock in share mode (as REQUEST=4).

Ses#1: commit;
Ses#2: commit;
Ses#1: ALTER TABLE tx_eg MAXTRANS 2;
        Ses#1:  update tx_eg set txt='First' where num=1;
        Ses#2:  update tx_eg set txt='Second' where num=2;

Both rows update as there is space to grow the ITL list to
accommodate both transactions.

Ses#1: commit;
Ses#2: commit;


Waits due to rows being covered by the same BITMAP index fragment
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  Bitmap indexes index key values and a range of ROWIDs. Each 'entry'
  in a bitmap index can cover many rows in the actual table.
  If 2 sessions wish to update rows covered by the same bitmap index
  fragment then the second session waits for the first transaction to
  either COMMIT or ROLLBACK by waiting for the TX lock in mode 4.

  Eg: Ses#1:  CREATE Bitmap Index tx_eg_bitmap on tx_eg ( sex );
        Ses#1:  update tx_eg set sex='FEMALE' where num=3;
        Ses#2:  update tx_eg set sex='FEMALE' where num=4;
        DBA:    select SID,TYPE,ID1,ID2,LMODE,REQUEST
                 from v$lock where type='TX';

SID        TY ID1        ID2        LMODE      REQUEST
---------- -- ---------- ---------- ---------- ----------
         8 TX     262151         62          6          0
        10 TX     327680         60          6          0
        10 TX     262151         62          0          4

This shows SID 10 is waiting for the TX lock held by SID 8 and it
wants the lock in share mode (as REQUEST=4).

Ses#1: commit;
Ses#2: commit;


Other Scenarios
~~~~~~~~~~~~~~~
  There are other wait scenarios which can result in a SHARE mode wait for a TX
  lock but these are rare compared to the examples given above.
  Eg: If a session wants to read a row locked by a transaction in a PREPARED
      state then it will wait on the relevant TX lock in SHARE mode (REQUEST=4).
      As a PREPARED transaction should COMMIT , ROLLBACK or go to an in-doubt
      state very soon after the prepare this is not generally noticeable..