1. Reducing Waits / Wait times:
The action to take depends on the lock type which is causing the most problems. It is beyond the scope of this reference note to look at the reasons for waits on each lock type - the most common lock waits are generally for:
     TX      Transaction Lock
                Generally due to application or table setup issues
                See Note:62354.1 for example scenarios which can cause
                TX lock waits.
LOCK: TX "Transaction Enqueue"
TX锁是表中一行上的行锁。事务为插入、更新、删除、合并或选择修改的每行获取行锁…FOR UPDATE语句。得出当一个事务开始首先改变并保持到事务提交或回滚呢。它主要用作队列机制，以便其他会话可以等待事务完成。这允许Oracle在事务开始之前构建块的一致读取映像。

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
下面提供了在各种情况下可能发生的TX锁和等待的示例。往往这样的等待会被忽视，除非他们是一个长时间或当他们触发死锁的情况（这就引发了一个ora-60错误）。
这些例子需要在v视图上选择特权。

什么是TX锁？
当事务发起第一个更改并被保留到事务提交或回滚时，将获得TX锁。它主要用作队列机制，以便其他会话可以等待事务完成。锁的名字（ID1和ID2）的TX锁反映成交活跃的交易ID。

--show all sessions waiting for any lock:
SELECT event, p1, p2, p3
FROM v$session_wait
WHERE wait_time= 0
AND event like 'enq%';

--从10G不同的更具描述性的事件名称存在更频繁的入队，可以查询发送等待事件如下：
SELECT sid, p1raw, p2, p3
FROM v$session_wait
WHERE wait_time = 0
AND event like 'enq: TX%';

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

--Show which segments have undergone the most row lock waits:
SELECT owner, object_name, subobject_name, value
FROM v$segment_statistics
WHERE statistic_name='row lock waits'
AND value > 0
ORDER BY 4 DESC;

+++++++++等待大约十分钟左右，然后再次运行该脚本。你可能比较相应的条目在“值”列中看到的对象的差异（S）经历了最行锁争用。+++++++++++++++++++++++++++++++++++++++
可以使用以下表演示可能发生的锁等待。
session1:
SQL> conn scott/tiger
Connected.
SQL>
SQL> 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;

Table created.

SQL>
1 row created.

SQL>
Commit complete.

EG:Waits due to Row being locked by an active Transaction
SQL>
SQL> UPDATE tx_eg SET txt='Garbage' WHERE num=1;

1 row updated.

session2:
SQL> conn scott/tiger
Connected.
SQL> UPDATE tx_eg SET txt='Garbage' WHERE num=1;

session3:
SQL> show user
USER is "SYS"
SQL> SELECT sid,type,id1,id2,lmode,request FROM v$lock WHERE type='TX';

       SID TY        ID1        ID2      LMODE    REQUEST
---------- -- ---------- ---------- ---------- ----------
        50 TX     655392        896          0          6
         1 TX     655392        896          6          0

SQL>

这表明SID 50正在等待SID 1持有的TX锁，它希望独占模式锁定（如请求＝6）。

SQL> SELECT sid, p1raw, p2, p3
2 FROM v$session_wait
3 WHERE wait_time = 0
4 AND event = 'enqueue';

no rows selected

SQL> select event,count(*) from v$session_wait group by event;

EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
SQL*Net message from client                                               1
enq: TX - row lock contention                                             1
pmon timer                                                                1
rdbms ipc message                                                        17
smon timer                                                                1
Streams AQ: qmn slave idle wait                                           1
SQL*Net message to client                                                 1
Space Manager: slave idle wait                                            1
VKTM Logical Idle Wait                                                    1
Streams AQ: qmn coordinator idle wait                                     1
Streams AQ: waiting for time management or cleanup tasks                  1

EVENT COUNT(*)
---------------------------------------------------------------- ----------
DIAG idle wait 2

12 rows selected.
SQL> SELECT sid, p1raw, p2, p3
2 FROM v$session_wait
3 WHERE wait_time = 0
4 AND event='enq: TX - row lock contention';

SID P1RAW P2 P3
---------- ---------------- ---------- ----------
50 0000000054580006 655392 896

SQL> SELECT row_wait_obj#,
2    row_wait_file#,
3    row_wait_block#,
4    row_wait_row#
5 FROM v$session
6 WHERE sid=10;

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
-1 0 0 0

The waiter is waiting for the TX lock in order to lock row 0 in file 0, block 0 of object -1.

session1:

SQL> commit;

Commit complete.

session2:
SQL> UPDATE tx_eg SET txt='Garbage' WHERE num=1;

1 row updated.

session3:
SQL> SELECT sid,type,id1,id2,lmode,request FROM v$lock WHERE type='TX';

SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
50 TX 196636 887 6 0
-----------session2获得了TX锁---------------------

因为唯一或主键约束执行而等待
如果一个表具有主键约束、唯一约束或唯一索引，则由约束引用的列的唯一性由一个惟一索引强制执行。如果两次尝试插入相同的键值第二会话必须等待，看ORA-0001是不是会增高
session1：
SQL> ALTER TABLE tx_eg ADD CONSTRAINT tx_eg_pk PRIMARY KEY( num );

Table altered.
SQL> INSERT INTO tx_eg VALUES (10,'New','MALE');

1 row created.

session2:
SQL> INSERT INTO tx_eg VALUES (10,'OtherNew',null);

session2等待

session3：
SQL> SELECT sid,type,id1,id2,lmode,request FROM v$lock WHERE type='TX';

       SID TY        ID1        ID2      LMODE    REQUEST
---------- -- ---------- ---------- ---------- ----------
        50 TX     589842        887          0          4
        50 TX     262145        860          6          0
         1 TX     589842        887          6          0

这表明SID 50正在等待SID 1持有的TX锁，它希望共享模式中的锁（作为请求＝4）。SID 50为自己的事务持有一个TX锁。

session1：
SQL> commit;

Commit complete.

session2：
SQL> INSERT INTO tx_eg VALUES (10,'OtherNew',null);
INSERT INTO tx_eg VALUES (10,'OtherNew',null)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.TX_EG_PK) violated

SQL>
SQL> rollback;

Rollback complete.

session3：
SQL> SELECT sid,type,id1,id2,lmode,request FROM v$lock WHERE type='TX';

no rows selected

由于在一块不足等“ITL”槽
Oracle保存在哪个数据块顶部的一个区域中哪些事务被锁定的记录，称为“感兴趣事务列表”。在一个对象中的任何块ITL槽的数量是由INITRANS和maxtrans属性。initrans是最初创建在一块时，首先使用的时隙数，而maxtrans地上界参赛人数允许。每个事务要修改一个块的块需要在这个“ITL”名单插槽。maxtrans地上界的并发事务可以活跃在任何时间点在一个块的数量。initrans提供最低保证每个块的并发。如果超过initrans但小于maxtrans交易要活跃的同时，在同一块然后ITL名单将扩展只要有可用做块内的空间，如果没有自由的ITL”然后请求的会话将等待一个在模式4的活动事务的锁。

session1：
SQL> UPDATE tx_eg SET txt='Garbage' WHERE num=1;

1 row updated.

SQL>

session2:
SQL> UPDATE tx_eg SET txt='Different' WHERE num=2;

1 row updated.

session3:
SQL> SELECT sid,type,id1,id2,lmode,request FROM v$lock WHERE type='TX';

       SID TY        ID1        ID2      LMODE    REQUEST
---------- -- ---------- ---------- ---------- ----------
        50 TX      65557        898          6          0
         1 TX     393244       1075          6          0

sid 50,sid 1各自持有一个TX lock

session1：
SQL> commit;

Commit complete.

session2：
SQL> commit;

Commit complete.

session1：
SQL> ALTER TABLE tx_eg MAXTRANS 2;

Table altered.
SQL> UPDATE tx_eg SET txt='First' WHERE num=1;

1 row updated.

session2：

SQL> UPDATE tx_eg SET txt='Second' WHERE num=2;

1 row updated.
有成长空间的ITL列表容纳交易更新行。

session1:
SQL> commit;

Commit complete.

session2:
SQL> commit;

Commit complete.

session3:
SQL> SELECT sid,type,id1,id2,lmode,request FROM v$lock WHERE type='TX';

no rows selected

你可以检查ITL等待V$segment_statistics与查询一样：
SQL> SELECT t.owner,
2    t.object_name,
3    t.object_type,
4    t.statistic_name,
5    t.value
6 FROM v$segment_statistics t
7 WHERE t.statistic_name = 'ITL waits'
8 AND t.value > 0;

no rows selected

在早期版本的Oracle数据库的maxtrans参数限制交易条目，可以同时在一个数据块使用的数据的数量。这个参数已在10G和更高版本已经被使用。Oracle数据库现在自动为每个数据块提供多达255个并发更新事务，这取决于块中的可用空间。
ITL的论点是可能产生等待的询问：TX -分配ITL进入等待事件。如果需要，增加inittrans和maxtrans解决这。在下面的文章中有更多关于如何排除这些等待的问题：

由于同一位图索引片段所覆盖的行而等待
位图索引索引键值和一系列的rowid。位图索引中的每个条目都可以覆盖实际表中的许多行。如果2个会话希望更新同一位图索引片段所覆盖的行，则第二个会话将等待第一个事务提交或回滚，等待模式4中的TX锁。
session1：
SQL> CREATE BITMAP INDEX tx_eg_bitmap ON tx_eg ( sex );

Index created.

SQL> UPDATE tx_eg SET sex='FEMALE' WHERE num=3;

1 row updated.

session2：
SQL> UPDATE tx_eg SET sex='FEMALE' WHERE num=4;

session3：
SQL> SELECT sid,type,id1,id2,lmode,request FROM v$lock WHERE type='TX';

       SID TY        ID1        ID2      LMODE    REQUEST
---------- -- ---------- ---------- ---------- ----------
        50 TX     458754        894          0          4
        50 TX     131103        882          6          0
         1 TX     458754        894          6          0

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

session1:
SQL> commit;

Commit complete.

session2:
SQL> commit;

Commit complete.

How to find which segments have undergone the most row lock waits using given range of AWR snapshot ID's:
SELECT P.snap_id,
P.begin_interval_time,
O.owner,
O.object_name,
O.subobject_name,
O.object_type,
S.row_lock_waits_delta
FROM dba_hist_seg_stat S,
dba_hist_seg_stat_obj O,
dba_hist_snapshot P
WHERE S.dbid               =O.dbid
AND S.ts#                  =O.ts#
AND S.obj#                 =O.obj#
AND S.dataobj#             =O.dataobj#
AND S.snap_id              =P.snap_id
AND S.dbid                 =P.dbid
AND S.instance_number      =P.instance_number
AND S.row_lock_waits_delta > 0
AND P.snap_id BETWEEN      <begin_snap> AND <end_snap>
ORDER BY 1,3,4;

ITPub博客

LOCK: TX "Transaction Enqueue"

最新文章