Recently i have been informed of, lot of 'ORA-00054: resource busy and acquire with NOWAIT' flooding the application log. Also i found the statement similar as below.

select c1 from
tab1 where c2='ABC' and c3=1 order by c3,c4 for update nowait;

So what i believe, is perhaps multiple sessions trying to UPDATE the same row which is locked by this above session
or
Some another session trying to execute the same statement (Select ... For Update) for the same row and might be this functionality executing in a loop and keep on executing till the time it gets the lock.. causing app log flooding with 'ORA-00054: resource busy and acquire with NOWAIT'

So my question
1) whether it will cause any performance issue going forword, if this error will flood the app log like this and if this error can be fixed by any means? any better way of getting the lock..
2) how can i get all the history(Past) of occurrence of this error and the exact blocked statement+blocking statement from DB?

These are filed under "application" class wait events for a reason - they're not the database doing anything funny, just what it has been told to do.

You need to engage with your developers about why they are employing pessimistic locking. ASH, if you have it, will give you the (partial) blocking session history but not necessarily the command they locked the rows with however again, the devs ought to be able to help you there.

In simple terms you've got a database telling you the application is locking itself - go speak to the application guys

if DBMS_LOCK.REQUEST(v_lock, DBMS_LOCK.x_mode, 35, TRUE)=0
THEN
UPDATE tab1
SET ...
WHERE ....
COMMIT;
END IF;

END;
/

2.Call below statement

Select c1 from
tab1 where c2='ABC' and c3=1 order by c3,c4 for update nowait;

Then Errored out with ORA-00054.

So i am thinking of like, if the first session taking the lock and executing the 'UPDATE' statement but not committed yet, then after anoher session comes and follows the same path , it calls the function but not able to take the lock(request), and skips the UPDATE statement, but then when it comes for executing second statement results i.e. 'SELECT .... FOR UPDATE' it fails with ORA-00054 error . But what i found is the 'UPDATE' statement executes within seconds.

So i am not confident, if this is what flooding the app log with error?
Again, at which situation would 'DBMS_LOCK.REQUEST' will return non zero value? I am always getting zero return value from this in my local.

We are applying pessimistic locking just before executing the 'Select .. For update' from java ,as below

Are you saying the app is using dbms_lock, doing an update and then selecting the updated rows with select for update?
That seems odd. Why is the app using these two different approaches to locking together?

VIP2013 wrote on Tue, 19 November 2013 16:52

So i am thinking of like, if the first session taking the lock and executing the 'UPDATE' statement but not committed yet, then after anoher session comes and follows the same path , it calls the function but not able to take the lock(request), and skips the UPDATE statement, but then when it comes for executing second statement results i.e. 'SELECT .... FOR UPDATE' it fails with ORA-00054 error . But what i found is the 'UPDATE' statement executes within seconds.

You will not be getting ORA-00054 as a result of the dbms_lock call. Dbms_lock does not lock actual rows in a table.
If the SELECT FOR UPDATE is raising the error then it's because rows in tab1 are locked. The update will do that, the select for update will do that, dbms_lock won't

VIP2013 wrote on Tue, 19 November 2013 16:52

So i am not confident, if this is what flooding the app log with error?

It could well be too different sessions running the same select for update.

VIP2013 wrote on Tue, 19 November 2013 16:52

Again, at which situation would 'DBMS_LOCK.REQUEST' will return non zero value? I am always getting zero return value from this in my local.

I got the error reported by application guys again for around ~1 hrs+ , so then i just executed the 'Select For.. UPDATE..' from my own sessions and tried to get the exact statement which is blocking me. Then i found a session showing DML lock on table tab1, but the current statement is showing as 'select 1 from dual' and the wait event was 'SQL *NET MEssage from client'. Also the same session was not releasing the lock till ~3 hrs, then app guys restarted the JMS server, after which that session got acquired by another process and start processing, as we have connection pooling implemented, .

So here i was unable to find the reason behind the blocking session, taken lock but doing nothing.. and its causing other processes to lineup and flooding error in the log.

I got the error reported by application guys again for around ~1 hrs+ , so then i just executed the 'Select For.. UPDATE..' from my own sessions and tried to get the exact statement which is blocking me. Then i found a session showing DML lock on table tab1, but the current statement is showing as 'select 1 from dual' and the wait event was 'SQL *NET MEssage from client'. Also the same session was not releasing the lock till ~3 hrs, then app guys restarted the JMS server, after which that session got acquired by another process and start processing, as we have connection pooling implemented, .

So here i was unable to find the reason behind the blocking session, taken lock but doing nothing.. and its causing other processes to lineup and flooding error in the log.

The currentstatement may have been a simple SELECT against dual, but what's to say that wasn't preceded in the same session with the SELECT FOR UPDATE, and the session still hadn't committed -- waiting on the user to get back from lunch.