Oracle Wait Event Tx Transaction

TX, Transaction

http://www.toadworld.com/platforms/oracle/w/wiki/826.tx-transaction.aspx

TX, Transaction

TX enqueues are used to serialize the database transactions and to get a consistent view throughout the lifecycle of the transaction. The row level locks are implemented using the TX enqueue where the change vectors to reverse the transactions are written in the undo segments and the address of the undo blocks are written in the data blocks so that the reader (or process) looking for a consistent view can follow the address and get the consistent view of that data duding that point in time.

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

To show all sessions waiting for any lock:

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

To show sessions waiting for a TX lock:

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

To show sessions holding a TX lock:

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

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.

This enqueue is typically used during transactions and index block splits. Sometimes heavy contention for TX enqueue could be an indication of bad application design which unnecessarily lock the rows. Index of the montonically-increasing key would be increasing the contention for the TX enqueue. Waits for the ITLs (Interested Transaction Lists) would also be an indicators of the TX waits. The waits for the transaction slots can be easily identified with querying V$SEGMENT_STATISTICS.

SELECT OWNER, OBJECT_NAME from V$SEGMENT_STATISTICS
where STATISTIC_NAME ='ITL waits' and VALUE > 0

If more waits are seen for 'ITL waits' event, the objects needs to be rebuilt using higher INITRANS. Keep in mind each initrans will take 24 bytes of space in the variable header of the data block and having more initrans potentially wastes space in the data blocks at the cost of concurrency.

In summary there are 4 cases where TX contentions can be seen and each of them are explained below.

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.

Session #1: update EMP set SAL=100 where emp_num=1;
Session #2: update EMP set SAL=1000 where emp_num=1;

When we query the V$Lock

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 or RAC 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

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:

SQL> select ROW_WAIT_OBJ#, ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
rom 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.

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.

Session 1: ALTER TABLE EMP ADD CONSTRAINT EMP_pk PRIMARY KEY( num );
Ses#1: insert into EMP values (10,'New','MALE');
Ses#2: insert into EMP 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.EMP_PK) violated

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.

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

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

Acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK. Also acquired exclusive by SMON when doing recovery (undo) of a transaction. Used as a queuing mechanism for processes waiting.

enq和TX

http://www.net527.cn/shujukuguanli/Oracle/2012/0721/23937.html

enq是一种保护共享资源的锁定机制,一个排队机制,先进先出(FIFO)

发生TX锁的原因一般有几个

1.不同的session更新或删除同一个记录;

2.唯一索引有重复索引;

3.位图索引多次更新;

4.同时对同一个数据块更新;

5.等待索引块分裂.

These are acquired exclusive when a transaction initiates its first change and held until the transaction does aCOMMITorROLLBACK.

Waits for TX in mode 6: occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait eventenq:TX-rowlockcontention.

The solution is to have the first session already holding the lock perform. aCOMMITorROLLBACK.

Waits for TX in mode 4 can occur if the session is waiting for an ITL (interested transaction list) slot in a block. This happens when the session wants to lock a row in the block but one or more other sessions have rows locked in the same block, and there is no free ITL slot in the block. Usually, Oracle dynamically adds another ITL slot. This may not be possible if there is insufficient free space in the block to add an ITL. If so, the session waits for a slot with a TX enqueue in mode 4. This type of TX enqueue wait corresponds to the wait eventenq:TX-allocateITLentry.

The solution is to increase the number of ITLs available, either by changing theINITRANSorMAXTRANSfor the table (either by using anALTERstatement, or by re-creating the table with the higher values).

Waits for TX in mode 4 can also occur if a session is waiting due to potential duplicates inUNIQUEindex. If two sessions try to insert the same key value the second session has to wait to see if anORA-0001should be raised or not. This type of TX enqueue wait corresponds to the wait eventenq:TX-rowlockcontention.

The solution is to have the first session already holding the lock perform. aCOMMITorROLLBACK.

Waits for TX in mode 4 is also possible if the session is waiting due to shared 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 two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to eitherCOMMITorROLLBACKby waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait eventenq:TX-rowlockcontention.

Waits for TX in Mode 4 can also occur waiting for aPREPAREDtransaction.

Waits for TX in mode 4 also occur when a transaction inserting a row in an index has to wait for the end of an index block split being done by another transaction. This type of TX enqueue wait corresponds to the wait eventenq:TX-indexcontention.

enq: TX - row lock contention与FK

外键没索引,当对主表做DML操作时,会对子表产生共享行排他表级锁(SSX),此锁会阻塞其它事务对子表DML操作,此时需等待释放子表的ssx锁,等待过程会有enq: TX - row lock contention 事件。
外键有索引,当对主表做DML操作时,会对子表产生行共享表级锁(SS),此锁不允许对子表加排他锁,但DML操作允许