Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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.

share|improve this answer
    
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

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.

share|improve this answer
1  
Commiting in a loop is not a good idea it makes things slower: asktom.oracle.com/pls/apex/… –  a_horse_with_no_name Jul 18 '12 at 21:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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