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?
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.