Solved

How to resolve "Resouce Busy" error generated by Coldfunsion function

Posted on 2016-08-01
20
66 Views
Last Modified: 2016-09-19
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.

   Thanks, and Regards,
resource_busy_error.txt
idListGenerationDAO.txt
ADDIDLIST.txt
0
Comment
Question by:henderxe
  • 9
  • 8
  • 3
20 Comments
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
0
 

Author Comment

by:henderxe
Comment Utility
dgrafx:

    Many thanks for your response - much appreciated.

   I had seen both of the links you cited above, but was not quite sure how, or if they could be implemented in this application.

     For example, I understand the role of the "LOCK TABLE 'TABLE_NAME' NO WAIT" function, and have several questions:

    1.  Is it performed (or modified) on the Oracle server, or within the application?

    2.  How does one issue your own session lock prior to activation of the above function, and will it work for all users of the application?

    3.  Finally,  does it just simply remove the "Resource Busy" error, or can multiple users process their queries simultaneously against the locked table?

    Regards,
0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
1. It is locked automatically
http://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF55502

To better help you solve your issue I would need to understand more about your application.

Read this: https://community.oracle.com/thread/634676?tstart=0

I can tell you that a session lock in CF doesn't affect the DB.
0
 

Author Comment

by:henderxe
Comment Utility
dgrafx:

    Sorry for the delay!

    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.

    Thank you!
0
 
LVL 24

Accepted Solution

by:
dgrafx earned 250 total points
Comment Utility
OK - so you have a lot of hits to the DB here.
To find what code needs to be commented out for testing one needs to understand more about your application.
I would need to have your datasource set up and your code set up and running to trace this.

Here is a link to a tool that should help you track down where this is taking place: https://docs.oracle.com/cd/B19306_01/server.102/b14211/perf_overview.htm#g1009625

The error points to the ADDIDLIST procedure so start with finding where it is called in your app ...
0
 

Author Comment

by:henderxe
Comment Utility
dgrafx:

   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!
0
 

Author Comment

by:henderxe
Comment Utility
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 ---------------------------------------
0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
Here is a general link: https://www.google.com/#newwindow=1&q=open+source+oracle+monitoring+tools

I'm not an Oracle guy myself.

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
0
 

Author Comment

by:henderxe
Comment Utility
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!
0
 

Author Comment

by:henderxe
Comment Utility
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.

   Thanks!
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
OK I don't know so try this

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;
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 250 total points
Comment Utility
  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.

Disclaimer, I'm not an Oracle guy either but .. you've probably discovered in your searches, and from the threads dgrafx posted, that there are system tables you can easily query to determine which objects are locked and why.  Have you or the DBA actually checked those tables and what did they find?  (I'm guessing only the DBA has access to the sys tables). Sorry if you mentioned the outcome already, but I didn't see it above...

Also, silly question but is "tempIdlists" actually a global temp table as the name implies, or is it a regular permanent table?
0
 

Author Comment

by:henderxe
Comment Utility
dgrafx:

    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.

   Thanks!

------------------------------------------------------------------------

agx:

  Thanks for your response - much appreciated.

  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.
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
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.
0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
AND if it was working fine for a year ...

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.
0
 

Author Comment

by:henderxe
Comment Utility
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.

    Regards
0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
another experiment

this article speaks of privledges and a tad different syntax
http://stackoverflow.com/questions/21995951/truncate-and-insert-within-procedure-dont-work-together
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
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.
0
 

Author Comment

by:henderxe
Comment Utility
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!
0
 
LVL 24

Expert Comment

by:dgrafx
Comment Utility
NP ...
Thanks for the points!
I hope you find a solution.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now