2

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?

3

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... – Arunabh Jul 7 '12 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. – a_horse_with_no_name Jul 7 '12 at 11:09
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.