The database I'm working on has session waits due to DMLs on numerous tables in a procedure.
The DBA identifies this as enq: TX - row lock contention.
How can I resolve this issue?
Add a comment
|
2 Answers
You are holding locks due to an UPDATE statemnt. Those locks are released when you commit your transaction.
To solve this issue, make sure you commit your transactions as soon as possible.
-
All these Update statements are inside a For loop. If I put a COMMIT inside the loop it will commit each and every thing. Is it acceptable committing each and everything one by one, what if there occurs an error in between. Dont we generally put a Commit as the last statement inside a procedure and ROLLBACK inside exception block. If I put a commit inside that for loop, putting Rollback in exception block becomes redundant. Isn't it? I hope you got my doubt...– ArunabhCommented Jul 7, 2012 at 11:05
-
@Arunabh You should not commit each individual statement you should commit your transaction - but that as soon as possible.. Any update that is not committed will block other updates (or deletes) on that row. If that happens the "row lock contention" is reported. It's a problem of concurrent update to the same row, not of multiple updates from a single transaction.– user1822Commented Jul 7, 2012 at 11:09
If you have a LOT of updates in your FOR loop, one idea is to put an internal counter variable in the FOR loop to commit every X number of updates.
EX. I have 50 million rows to be updated. My FOR loop works through each individual update. Instead of waiting until all 50 million updates are done to commit (and avoiding your UNDO to grow), commit every 1-5 million rows. That gets your updates done, allows a reasonable ROLLBACK window for errors, and will allow that other transaction holding the lock to slip in.
-
1Commiting in a loop is not a good idea it makes things slower: asktom.oracle.com/pls/apex/…– user1822Commented Jul 18, 2012 at 21:55