enq: TX - Row Lock Contention

遇到了一个 enq: TX - row lock contention的问题，从tkprof看，update真正执行的时间(cpu)很快，但elapsed时间非常长，在最后发现 enq: TX - row lock contention时间很长。

UPDATE MTL_ITEM_LOCATIONS SET STATUS_ID = :b1
WHERE
INVENTORY_LOCATION_ID = :b2


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 10 0.01 299.84 2 4 7 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.01 299.84 2 4 7 2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 195 (APPS)
Number of plan statistics captured: 2

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 UPDATE MTL_ITEM_LOCATIONS (cr=0 pr=0 pw=0 time=8 us)
0 0 0 TABLE ACCESS BY INDEX ROWID MTL_ITEM_LOCATIONS (cr=0 pr=0 pw=0 time=6 us cost=3 size=96 card=1)
0 0 0 INDEX RANGE SCAN MTL_ITEM_LOCATIONS_U1 (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)(object id 560359)


Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT MODE: ALL_ROWS
0 UPDATE OF 'MTL_ITEM_LOCATIONS'
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'MTL_ITEM_LOCATIONS' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'MTL_ITEM_LOCATIONS_U1' (INDEX (UNIQUE))


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 10 0.00 0.00
SQL*Net message from client 10 0.00 0.00
db file sequential read 2 0.00 0.00
enq: TX - row lock contention 1 299.83 299.83
********************************************************************************

分析

ERROR: enq: TX - row lock contention

TX是指行级锁,row lock contention指行级锁的争用

=> Row Locks (TX)
Row-level locks are primarily used to prevent two transactions from modifying the same row. When a transaction needs to modify a row, a row lock is acquired.

=> Table Locks (TM)
Table-level locks are primarily used to do concurrency control with concurrent DDL operations, such as preventing a table from being dropped in the middle of a DML operation

enq: TX - Row Lock Contention Error 里的说法，这个问题多发生在 multiple transaction attempt to update the same data blocks。

This occurs when one application is updating or deleting a row that another session is also trying to update or delete. This will generate a wait event "enq: TX - row lock contention". To solve this particular instance, the session holding the lock must perform a COMMIT or ROLLBACK.

解决方法：

最终找到问题的原因，是因为客户同时开了两个Form，同时使用一个Locator，第一个Form Session里的一个Update语句锁住了Locator，造成第二个Form Session hang在那。
解决方法：第一个Form Session在Update之后，执行commit。