Oracle中的锁，一共有6种模式：

0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁，sub share
3：Row-X 行独占(RX)：用于行的修改，sub exclusive
4：Share 共享锁(S)：阻止其他DML操作，share
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
6：exclusive 独占(X)：独立访问使用，exclusive

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 View:V$LOCK
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 the 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.

–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).

–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
> TX 6 13107 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.
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.

–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.

–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;

From 9.2 you can check the ITL Waits in v$segment_statistics with a query like:

SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE
FROM v$segment_statistics t
WHERE t.STATISTIC_NAME = ‘ITL waits’
AND t.VALUE > 0;

If need be, increase INITTRANS and MAXTRANS.

In earlier releases of Oracle Database, the MAXTRANS parameter limited the number of transaction entries that could concurrently use data in a data block. This parameter has been deprecated in 10g and higher. Oracle Database now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.

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.

–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.

Example:
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..