具有唯一索引的表更新引起的竞争分析：

文档叙述：

Waits for TX in mode 4 can also occur if a session is waiting due to potential duplicates in 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. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

SQL> create index t3_ind on t3(object_id);（唯一索引）

Index created.

SQL> analyze index t3_ind compute statistics;

Index analyzed.
session 1执行操作：
SQL> insert into t3
2 select * from ttt
3 where object_id=100;

1 row created.
session 2 执行同样的操作：
SQL> insert into t3
2 select * from ttt
3 where object_id=100;
session 2挂起。

分析：
SQL> select * from v$session_wait;

SID SEQ# EVENT P1TEXT
---------- ---------- ---------------------------------------------------------------- ---------------------
145 1 jobq slave wait
146 85 SQL*Net message from client driver id
147 336 SQL*Net message from client driver id
150 36 Streams AQ: qmn slave idle wait
152 3 Streams AQ: waiting for time management or cleanup tasks
154 6 Streams AQ: qmn coordinator idle wait
158 131 enq: TX - row lock contention name|mode
160 6 rdbms ipc message timeout
161 884 rdbms ipc message timeout
162 326 rdbms ipc message timeout
163 6 rdbms ipc message timeout

SID SEQ# EVENT P1TEXT
---------- ---------- ---------------------------------------------------------------- ---------------------
164 2656 smon timer sleep time
165 6047 rdbms ipc message timeout
166 4100 rdbms ipc message timeout
167 445 rdbms ipc message timeout
168 7 rdbms ipc message timeout
169 313 rdbms ipc message timeout
170 9 pmon timer duration

18 rows selected.

SQL> select sid, chr(bitand(p1, -16777216)/16777215) || chr(bitand(p1,16711680)/65535) "Name",
2 2* (bitand(p1, 65535)) "Mode" from v$session_wait where event like 'enq%'
3
SQL> select sid, chr(bitand(p1, -16777216)/16777215) || chr(bitand(p1,16711680)/65535) "Name",
2 (bitand(p1, 65535)) "Mode" from v$session_wait where event like 'enq%'
3 ;

SID Name Mode
---------- ---- ----------
158 TX 4

SQL> SELECT * FROM V$LOCK WHERE request > 0;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
1EC344AC 1EC344C0 158 TX 262180 420 0 4 93 0

SQL>
SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
2 sid sess, id1, id2, lmode, request, type
3 FROM V$LOCK
4 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
5 ORDER BY id1, request;

SESS ID1 ID2 LMODE REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 147 262180 420 6 0 TX
Waiter: 158 262180 420 0 4 TX

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9599/viewspace-472968/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9599/viewspace-472968/