Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Row lock with for update

SB35Jul 1 2021

Its version 19.9.0.0.0 of oracle exadata. We are seeing below application query which used to run around ~200-300 times in an hour and was used to take ~.5 sec/execution. But then after it started executing ~1000 times/hour and the execution time went up to ~3 to ~4 seconds/execution flooding the whole database with "row lock contention' application waits.
It seems this statement is taking a lock and doing some processing at the same time waiting for 20 seconds if not getting a lock. And the row lock contention appears when lot of sessions submitted doing same functionality and fighting with each other. So wanted to know, how should we fix such issue? Should we make it as NOWAIT and let the session skip that process when one is already working? or should we minimize the WAIT from 20 to ~10?
SELECT ID FROM LOCK_TAB WHERE LOCK_NAME = 'XXXX' FOR UPDATE OF ID WAIT 20

This post has been answered by Jonathan Lewis on Jul 5 2021
Jump to Answer

Comments

Post Details

Added on Jul 1 2021
28 comments
4,283 views