Home » RDBMS Server » Performance Tuning » Locking In application code.
| Locking In application code. [message #601256] |
Mon, 18 November 2013 23:18  |
 |
VIP2013
Messages: 91
Registered: June 2013
|
Member |
|
|
Hi all,
i am using 11.2.0.3.0 version of oracle.
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?
|
|
|
|
| Re: Locking In application code. [message #601273 is a reply to message #601256] |
Tue, 19 November 2013 02:20  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
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 
[Updated on: Tue, 19 November 2013 02:22] Report message to a moderator |
|
|
|
| Re: Locking In application code. [message #601320 is a reply to message #601273] |
Tue, 19 November 2013 10:52 |
|
VIP2013
Messages: 91
Registered: June 2013
|
Member |
|
|
i got some more information regarding the issue.
We are applying pessimistic locking just before executing the 'Select .. For update' from java ,as below
1. calling a function from java, which contains below code
begin
DBMS_LOCK.ALLOCATE_UNIQUE('LOCk_1', v_lock, null);
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.
|
|
|
|
| Re: Locking In application code. [message #601321 is a reply to message #601320] |
Tue, 19 November 2013 11:18 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
VIP2013 wrote on Tue, 19 November 2013 16:52
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.
The documentation will answer this, have you read it?
|
|
|
|
|
|
|
|
| Re: Locking In application code. [message #601606 is a reply to message #601419] |
Fri, 22 November 2013 17:04 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
VIP2013 wrote on Wed, 20 November 2013 14:06I 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 current statement 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.
|
|
|
|
Goto Forum:
Current Time: Mon Sep 25 05:59:01 CDT 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
