ORA-00054: resource busy and acquire with NOWAIT

Hello,

Basically my program crashed yesterday after it appeared to go into an infinite loop.   Now if I try and access the stored procedure in question, the program will just crash.  I have tried using the console and toad... both just crash out when I try to compile the sp.

The table (that the sp uses) is also in trouble... I can not delete anything from it (crash)
When I tried to drop the table I got the error message:
ORA-00054: resource busy and acquire with NOWAIT

I can only assume that this means some transaction is still open on the table, and that this is causing all the errors.   I have restarted both my test pc's but still to no avail.

Can you tell me how to end this transaction, can I not do it from the oracle server side?

any ideas?
obrienjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

konektorCommented:
you can find cause of problem only with acces to system catalogues (user sys, system, or any other having grants to following views): DBA_DDL_LOCKS, DBA_DML_LOCKS, DBA_LOCK, V_$LOCKED_OBJECT, DBA_BLOCKERS. If you find the session which blocks some objects u can use:
alter system kill session '<sid>,<serial#>';
<sid> and <serial#> corresponds values in v$session
0
RCorfmanCommented:
Or, instead of killing it, if you look in v#session, you can see the terminal and osuser who holds the lock... often you can try to contact them and request that they commit their sql statement, or logoff as appropriate... This is a better approach than a kill if you can get hold of them.
0
obrienjAuthor Commented:
take it that I am an oracle novice.... could ye explain to me how to find the info. Theres a lot of info in those tables.... how do I know whats locking what etc etc...

Also, I have no doubt I am in charge of the session that is locking this... which I dont understand as I no longer have any accessing program running - I have even restarded the machines etc.....
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

anand_2000vCommented:
select sid from v$lock where block <>0;

select sid,serial#,machine from v$session where sid=<the sid obtained from previous command>;

after verifying the sid of the connection

alter system kill session '<sid>,<serial#>'

for example
alter system kill session '9,182';
0
anand_2000vCommented:
please remember that you will require dba access for executing all the commands given by me above
0
vishal68Commented:
Correct me if I am wrong, But I believe you have written that you have restarted your system. Is that the system on which database is residing, if yes then all the locks should have been already released. The only locks that may remain even after a restart of the database are distributed transaction locks.  Is your SP doing distributed transactions ?

HTH
Vishal
0
obrienjAuthor Commented:
hey guys... thanks anand.

yep I found the session thats causing it... and I will kill it in a minute.... but I am a bit worried. Firstly I am not sure how this happened.  There may be something wrong with my setup....Is there a way to prevent this from happening?  Secondly, the machine that has this has been restarted... how has it still got a lock on the table?

vishal, no the machines I restarted are the Client machines.

I have access to the client, how can I find the open transaction on that and close it from there?
0
johnsoneSenior Oracle DBACommented:
The client terminated abnormally.  When this happens the connection can be left open on the server side, and Oracle will not detect that the session is lost.  If this is the case then pmon will not terminate the session and clean it up.  If the client that the connection originated from has been rebooted than it is safe to kill the connection and pmon should clean up the locks within about 5 minutes.

One thing to look into is enabling dead connection detection.  This may help in cleaning up the connection faster, but would not prevent the lock from happening.
0
obrienjAuthor Commented:
well I dont mind it happening as long as it gets cleaned automatically... obviously its not doing that at the moment... ill kill the connection for now... and look into setting that.
thanks guys

0
obrienjAuthor Commented:
okay.. I tried to kill the session but got the following error message:

ORA00031: session marked for kill

this seems to mean that it is still currently been used, but the machine that is been returned is actually SHUT DOWN at the moment... how is this possible?
0
RCorfmanCommented:
This is very possible.
When a transaction is in progress, Oracle retains 'rollback' information for the transaction. This  is in case the user uses
Rollback;
instead of
commit;

Oracle is optimistic. It has been designed under the assumption that the user will commit their transaction, so the work is actually done, mostly, for the transaction to complete. When you commit, it flags it all as done, and releases the rollback data.

If, instead, you issue a 'rollback', it has to UNDO everything that it has done before the transaction is 'finished'. Commit is very fast, rollback takes JUST AS LONG to under as the work that has been done so far.

When you kill a session, or turn off a computer connected as  a session, Oracle has to start rolling back the data. This can take a while.  This is what you see when you see a session has been marked for kill, the rollback is in progress for the work that was done.

Locks will clean themselves up automatically in normal operation, when the user issues the COMMIT or ROLLBACK. Until then, any updates/inserts/deletes, retain locks.
0
johnsoneSenior Oracle DBACommented:
Windows or UNIX?

select spid from v$process where addr =
(select paddr from v$session where sid = <sid>);

This query will tell you the process id of the dedicated connection.  Kill that process id.  On UNIX it is "kill -9 <spid>".  Not sure on the UNIX side.  This will force Oracle to clean it up faster.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RCorfmanCommented:
It still needs to rollback the data. this is pretty much the same as turning off the computer that is connected as a session. All the rules of rolling back the data still apply.
0
obrienjAuthor Commented:
okay... im finishing up now, and that has still not done any rolling back...and the session is still locked on.  I will leave it for the weekend and hope to god its okay when I come back in here on monday.  (but i have a feeling it won't be)

cheers,
james
0
RCorfmanCommented:
If a process spends 5 hours executing an update, it will take 5 hours to apply the rollback.  I know I was an EE post on this, but I can't find it... errr. It was a good explanation.
0
johnsoneSenior Oracle DBACommented:
Yes, pmon could be currently cleaning up the transaction, but killing the dedicated server process will definitely kick start it.

In theory it should take less time to roll back.  Especially if we are not talking about a single statement.  If there is processing that happens between statements, that does not have to be redone to roll back and the roll back should happen quicker.
0
RCorfmanCommented:
I couldn't find the post I was looking for that discussed this.... errrr.

I'm curious as to why Oracle would rollback faster if the process is killed at the OS level vs. being killed in the database vs. issueing a rollback command. I was always under the impression that a rollback is a rollback and the process takes as long as it takes... I am always wanting to learn more though and am curious to know how/why it would be faster.
0
johnsoneSenior Oracle DBACommented:
It all depends on how the update is done.

The undo log has change vectors, so no indexes are involved in the rollback, only direct access to the data files.

If the update is a single statement of every record in the table, then rollback would probably be equal.

If the update is a single statement based on an index lookup, rollback should be slightly faster.

If the update is part of a loop in a procedure/anonymous block/application then the rollback should be significantly faster.  Any processing and cursor management does not have to be undone, only the database changes.

Killing the process moves the rolling back from the dedicated server process to pmon.  Why is that faster?  Not sure.  Cleaning up processes is its main function and is definitely optimized for that.  It may also bypass some of the latching.  Of course, Oracle will not tell anyone how that works, it is "need to know" and you do not.  I have known people that work there that cannot get answers to some much simpler questions.

I have had transactions running for 4 hours that roll back in 45 minutes.  It is the nature of the transaction that dictates how long it will take to roll it back.
0
RCorfmanCommented:
I agree if you are doing lots of other processing in addition to the updates to the database, that processing time is skipped during the rollback so the rollback can be faster than the apply, but not if it is one single sql statement, or a series of datements with little lookups happening... basically all update into the undo spaces.

Unapplying them is as much work as applying them.

OK, you are correct, the kill at the OS can be faster, but it can also be dangerous and leave other stuff hanging out there.
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4974573906087

As always good stuff from Tom Kyte... he get's to know some of these details...;)
----not Tom, but another poster...------
Also, a warning for kill -9 lovers. NEVER use this on a process that has
allocated PQ slaves...the main process will go away, but not the PQ slave
processes, and nobody else will be able to allocate them. I found out the hard
way. Use the alter system command for the parent process, and the PQ slaves will
be de-allocated.
-------------------------------
Tom's comments that are relevant to this discussion...
no, pmon doesn't with alter system kill session -- the session does it. and does it in bits so as to not overwhelm the system .

Also, it normally takes longer to ROLLBACK than it did to get to do the work in the first place.

Until the CLIENT gets the ora-28, the session will remain in the killed state after it is done.


on the other hand, kill -9 makes pmon do it.  pmon will do it much like the session would have but can use parallel and even fast start (block level recovery upon demand) if possible.

This last comment from Tom explains your point on why it can be faster. I leave here now with more knowledge than I started with. Thanks johnsone.

0
johnsoneSenior Oracle DBACommented:
Sorry, I knew that pmon could take advantage of parallel recovery, I just forgot to write it down.

We don't use a lot of PQ, so that doesn't enter into the picture for us.  However, if you killed off the PQ slaves, you should accomplish the same thing.  Oracle would figure out that the PQ slaves were gone and restart them when needed.  This does happen normally, as when the PQ slaves are idle for a certain period of time (set by an init.ora parameter) they will naturally die.

Like I said, the time to rollback is based on the nature of the original transaction.  Having worked almost exclusively with OLTP for over 10 years, I have never seen undo take longer than to do the original transaction.
0
obrienjAuthor Commented:
okay, back to work now.   And still no change in the database.  I really doubt it's still rolling back.  There is something wrong.  
0
obrienjAuthor Commented:
okay... i'm not too sure whats going on here.
The session does actually seem to be killed, at least thats what it says in the session table. But the command select sid from v$lock where block <> 0; is still returning the sid of the killed session.


I am currenty looking at the sessions table.  There is 20 rows.  Each of these sessions is ACTIVE except for two.  One of which is INACTIVE and the other which is KILLED (this been the one I killed).

6 of these sessions are background sessions, the other 14 belong to my client pc.  While looking at this table I opened a new connection. it created a new row in the table.  When i logged out of sqlplus the row disappeared.   But for some reason this group of sessions is not disappearing. None of these connections are active on the client machines.  

0
obrienjAuthor Commented:
and by the way.... the following:

select sid from v$lock where block <>0;

is still returning the SID that I have killed, and the status of it in the session table is KILLED.

??
0
obrienjAuthor Commented:
hey okay I got it in the end..... I did have to use the kill -9 (or the windows equiv).  Once I did this on the trouble process then all the others cleaned themselves.  It took about 20 min for all to clear. thank feck for that.

but I am extremely worried about how this happened, and about how to prevent this in the future.
0
RCorfmanCommented:
If the one computer was actually turned off, pmon usually picks up on that and rolls it all back and cleans up. It isn't normal for it to hand on and have to be killed, but sometimes that happens. It is best if people exit gracefully... but we all know sometimes that isn't possible.  Having to kill at the OS level does, on occasion seem necessary.
0
johnsoneSenior Oracle DBACommented:
In theory pmon should clean it up.  However, due to the way Windows works, if the application does not disconnect and the user closes the window, it can leave sessions hanging that need to be killed.  Also, if the machine crashes it can leave sessions hanging.

Normally, they can be killed and are cleaned up pretty quickly.  Since this was in some sort of update that needed to be rolled back, I believe pmon will wait until the active query is done.  Killing the session at the OS level obviously kills it.

Again, dead connection detection will not prevent the process from going off the deep end, but it should kill it is the machine is rebooted.
0
helpneedCommented:
hi,,
if u have access to db find out the locked objects and kill therse process..it may take some time depends on your transaction ,i will aso take time to roll back ...after that it should be fine for you...


regards
0
anand_2000vCommented:
I anticipate that your database in on a unix/linux flavor OS. Whenever you start a client process it spawns a process in the server also. when you reboot the client the process in server is still running. What you can do is set sqlnet.expire_time=<number of minutes> in the sqlnet.ora file.
Another option will be to identify the spid using the following command
select spid from v$session s, v$process p where s.paddr=p.addr and s.sid=<sid which you have located>;
this will give you the server process id. (for example - 1192) then you can kill the process directly by using the command in the $ or # prompt
kill -9 1192
please do not do this if you are using shared server connections
regards
0
anand_2000vCommented:
I am not sure of the kill -9 equivalent in Windows. Can you tell me what it is?
0
obrienjAuthor Commented:
thanks anand... ill try the expire_time when I get the chance.
the windows equiv : orakill <sid> <spid>

thanks to all for your help, i will split the points amongst a few as it was almost everybodys input that got the result.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.