遇到了一个 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) |
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。