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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.