How to resolve "Resouce Busy" error generated by Coldfunsion function
Hello:
I'm experiencing an intermittent error, e.g. ORA-00054: resource busy and acquire with NOWAIT, which apparently prevents users from exporting files simultaneously. Don't recall this being an issue in the past, however. See attached Coldfusion error log file (resource_busy_error.txt). Actual errors are on lines 353 - 355, and 369 - 371.
Also, note that the error points to a stored procedure called "ADDIDLIST" (Line 6), which is also attached (ADDIDLIST.txt).
In addition, a CFC is attached (idlistgenerationDAO.cfc), which contains the functions (getIDList, and getExcludedIDList), both of which generate (and export) files at the same time. It may be the cause of this error. One file contains a mailing list of those eligible for a mailing (no restrictions), and the other lists those who were ineligible (restrictions existed).
As I mentioned, it's intermittent, and may function fine for weeks without an error, but sometimes it may fail for a couple of days in a row. I receive the error (via email) each time it occurs.
I'd appreciate any assistance you can provide.
Let me know if you need further clarification, or additional information.
Your information certainly defines the issue, as well as the solution. Also, it's helpful to hear that the locks in CF are not related to those in the DB. But, I'm still not clear on how, or where the fix should be implemented.
In terms of the application, the attached file (getSolicitCount.cfm), contains the two functions that actually generates the files (getSolicitCount, and getExcludedSolicitCount), Lines 15 - 32. They are called in the CFC file I uploaded previously.
The two files are then 'zipped" (Lines 38 - 41), and exported for access by the user. My understanding is that this may be a possible cause of the error. Each makes a call to the DB simultaneously.
This sure seems like the solution, though.
Let me know if you need more information, and/or clarification of the application.
Not having access to the Oracle Enterprise tools has been a challenge for me, as well as convincing the DBAs that the problem is database related, as opposed to application related.
Is there s good third party set of tools (e.g. open source) that I can use to gather the statistics I need to either confirm (or rule out) the database as the issue (although I'm convinced the problem is there).
Thanks!
henderxe
ASKER
dgrafx:
By the way, below is the stored procedure you mentioned (ADDIDLISTS).
The error is generated on Line 6.
------------------------- BEGIN STORED PROCEDURE ---------------------------------------
"create or replace procedure AddIdLists (id_no in clob) is
id_insert_val varchar2(8000);
id_pos int := 1;
id_pos1 int := 1;
begin
EXECUTE IMMEDIATE ' truncate table tempIdlists';
if dbms_lob.instr(id_no, ',', id_pos) = 0 then
execute immediate ' insert into tempIdlists(vals) values(:v)' using to_char(id_pos);
else
while id_pos < dbms_lob.getlength(id_no) loop
id_pos1 := dbms_lob.instr(id_no, ',', id_pos + 1);
if id_pos1 = 0 then -- last value in list
id_pos1 := dbms_lob.getlength(id_no) + 1;
end if;
id_insert_val := dbms_lob.substr(id_no, id_pos1 - id_pos, id_pos);
id_pos := id_pos1 + 1;
execute immediate ' insert into tempIdlists(vals) values(:v)' using id_insert_val;
end loop;
commit;
end if;
end AddIdLists;
----------------------- END STORED PROCEDURE ---------------------------------------
Try deleting all rows instead of truncating the table
DELETE FROM tempIdlists
Are you trying to rollback under certain circumstances?
Because you cannot rollback truncate but can rollback delete
henderxe
ASKER
dgrafx:
Nope - no rollbacks as far as this particular feature.
Will try the DELETE function, as well as one of those tools on the Oracle side!
Thanks!
henderxe
ASKER
dgrafx:
A quick update!
Tried the delete option, and the errors increased significantly, so set it back to truncate.
Evaluating a couple of monitoring tools which provide great information, but not sure what I'm looking for in terms of identifying table" locks", and the cause.
Let me know if there's a specific feature and/or function I can focus on to determine what may be causing the "Resource Busy" error.
begin
EXECUTE IMMEDIATE ' truncate table tempIdlists';
end
begin
if dbms_lob.instr(id_no, ',', id_pos) = 0 then
execute immediate ' insert into tempIdlists(vals) values(:v)' using to_char(id_pos);
else
while id_pos < dbms_lob.getlength(id_no) loop
id_pos1 := dbms_lob.instr(id_no, ',', id_pos + 1);
if id_pos1 = 0 then -- last value in list
id_pos1 := dbms_lob.getlength(id_no) + 1;
end if;
id_insert_val := dbms_lob.substr(id_no, id_pos1 - id_pos, id_pos);
id_pos := id_pos1 + 1;
execute immediate ' insert into tempIdlists(vals) values(:v)' using id_insert_val;
end loop;
commit;
end if;
I'm running your version of the ADDIDLISTS stored procedure, and haven't detected any additional issues with my system, thus far.
However, several other applications use thisdatabase, and since last Thursday, the server had to be re-booted three time due to dozens of runaway (or orphaned) sessions. The result is a '503' error, and no one can access the database until it's re-booted.
Needless to say, the DBA has been catapulted into the limelight, but still maintains that the applications are the issue. The problem with that argument is that this application (and others) have been running fine for years, and haven't changed in the past year.
At any rate, I'm gather what appears to be significant information from the r serverusing one of the programs from your list. I'll upload a file with the results, and and perhaps you can spot something out of place.
It is indeed a tad frustrating trying to make your case to a DBA who simply makes assumptions without any analysis of the database. As a minimum, I would think that he'd take the steps to rule out the database as the issue. Nothing so far.
Answers to your questions follow:
1. I attempted to check the tables, and get an "access denied". The DBA just contends that the DB is not the issue. I haven't seen anything (e.g. reports, statistics) to support that theory. Perhaps tomorrow.
2. Yes - the table "tempIdlists" is both temporary, and global.
Thanks for your input.
_agx_
EDIT: Added question
The DBA just contends that the DB is not the issue.
Without saying whether or not they investigated the system tables to discover the chain of events and possible causes of the blocking?
Yes - the table "tempIdlists" is both temporary, and global.
Not to beat a dead horse, but are you 100% positive (and does it PRESERVE ROWS or DELETE ROWS on commit) ? I thought GTT data was essentially specific to each session, so to have blocking problems sounds .. odd. I could see it happening with a regular table though.
Out of curiosity, what does the "execute immediate" buy you with the insert statements? I don't know that it makes any difference, I just thought those types of statements could be done without exec, even in a proc.
WHAT changed with the DB if anything? Service packs? anything?
It sounds like you'll need to bring your case to someone higher up that can force the DB to run the proper tests to determine the cause.
henderxe
ASKER
agx:
As far as I can tell, the rows appear to be DELETED upon issuing a "COMMIT" . The table needs to clear existing data prior to the next user's request. I'm certain about the table being global (used by other functionality throughout the application), however, "temporary" may be debatable.
The table (tempidlists) resides "permanently" in the DB, as opposed to being created dynamically. I don't see anywhere in the code where it is dropped and/or re-created, so you may be on to something.
My understanding is that the "execute immediate" command is more suited for not only building very complex dynamic SQL, but also executing it right away. Users have numerous options to choose from, and output is often huge. But to your point, it could likely be performed without the use of the "execute immediate" statement. Are you thinking that using this statement is a potential issue?
dgrafx:
I'm sure it's one, or more of the above (changes on DB side).
We are not recipients of the information, and pretty much anytime one of our programs (especially this one) becomes unstable, the application is always the culprit. Absolutely nothing was changed on the application.. It would also be helpful if the error were more consistent. Until today, the error hadn't occurred since August 25th.
Those affected mostly are outside of our building, but the voices are getting louder (irate users).
By the way, the program I was running to gather statistics on the database (MyOra) has not been very effective, at this point, primarily because the error is not consistent, and other issues with the dDB.
Thank you both very much - appreciate your thoughts, and the dialog.
don't see anywhere in the code where it is dropped and/or re-created
Well that would apply to GTT's too. They're only created once. Coming from SQL Server I remember finding it odd too, but it's the way Oracle does it.
As far as I can tell, the rows appear to be DELETED upon issuing a "COMMIT"
Hm... if it is truly is a GTT, seems like it would be set to PRESERVE. Otherwise, what's the point of the DELETE/TRUNCATE? Because (I believe) the records would automatically disappear at the end of the transaction and/or commit's in AddIdLists.
Are you thinking that using this statement is a potential issue
Not sure. I got the impression it was designed more for dynamic sql, or situations were you couldn't represent certain statements in pl/sql. Since that's not the case in your proc, I was curious if there was a specific reason it was used here - and if removing it made any difference.
henderxe
ASKER
dgrafx, and agx:
I'm very sorry for the delay. Had a class all of last week (Informatica) which dominated much of my focus - but now an excuse!
Just wanted to make a comment before closing this out.
I'll follow up on your suggestion of moving the question to the Oracle folks, but know that your comments, and analysis have been very helpful in exploring considerations, as well as things I can rule out.
Thanks again - much appreciated!
dgrafx
NP ...
Thanks for the points!
I hope you find a solution.
Read these two links:
http://stackoverflow.com/questions/3075738/ora-00054-resource-busy-and-acquire-with-nowait-specified
http://stackoverflow.com/questions/4842765/ora-00054-resource-busy-and-acquire-with-nowait-specified-or-timeout-expired