Home » SQL & PL/SQL » SQL & PL/SQL » Parent/Child Request trying to incombaitibly lock object
Parent/Child Request trying to incombaitibly lock object [message #188017] Wed, 16 August 2006 14:41 Go to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
I was not sure were to put this, it seems like its a little bit Server Admin, a bit E-Busieness Suit and a bit PL/SQL all depending on what is actually causing my problem...which is what I don't know so I figured PL/SQL Experts was a good catch all.

So the issue:

We have a package that conatins the code for several concurent requests. We have one 'main' request which operates in this geneal way:

Stag 1: Set-Up the other concurent requests (actually register them in the system)

Stage 2: Fire a concurent request (based on code in the same package) which makes backup copies of threee tables

Stage 3: Fire a concurent request (again based on code in the same package) which creates a temporary storage table.

Stage 4: Fire another concurent request (again using code in the same package) which creates two indexes on the table from stage 3.

For some reason, the concurent request fired in stage 4 ends up gridlocked with the initial concurent request. Our DBA did some analysis of what is going on and it looks like the session for each of the requests is trying to get a PIN to the package which contains all of the source code. The Stage 4 Request is trying to PIN the package object in exclusive mode, and the initial parent session is trying to PIN it in shared mode.

At this point the whole question is academic because we had to simply pull stage 4 out and perform it manually, but I am curious why stage 4 would need an exclusive PIN on the object in the first place. This is the stage 4 code:

PROCEDURE optimize_tmp_table (errorbuf OUT VARCHAR2, retcode OUT VARCHAR2)
   IS
      eid_index        VARCHAR2 (1000) := 'CREATE INDEX tmp_cdl_n1 ON tmp_cdl(expenditure_item_id)';
      line_num_index   VARCHAR2 (1000) := 'CREATE INDEX tmp_cdl_n2 ON tmp_cdl(line_num)';
   BEGIN
      fnd_file.put_line (fnd_file.output, '===================================================================');
      fnd_file.put_line (fnd_file.output, '                     Optimizing tmp_cdl table                      ');
      fnd_file.put_line (fnd_file.output, '===================================================================');
      fnd_file.put_line (fnd_file.output, '');
      fnd_file.put_line (fnd_file.output, 'Creating index on expenditure_item_id....');

      EXECUTE IMMEDIATE eid_index;

      fnd_file.put_line (fnd_file.output, '....SUCCESS');
      fnd_file.put_line (fnd_file.output, '');
      fnd_file.put_line (fnd_file.output, 'Creating index on line_num....');

      EXECUTE IMMEDIATE line_num_index;

      fnd_file.put_line (fnd_file.output, '....SUCCESS');
   EXCEPTION
      WHEN OTHERS
      THEN
         BEGIN
            raise_application_error (-20001, SUBSTR (SQLERRM, 1, 100));
         END;
   END optimize_tmp_table;


Any ideas? I can understand why this code might want exclusive access to the table it is trying to index, but why the package that contains itself?

Thanks for any help,
Andrew
Re: Parent/Child Request trying to incombaitibly lock object [message #188056 is a reply to message #188017] Wed, 16 August 2006 21:35 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
OK, I'll take a guess. Is it doing file output? If so, you wouldn't want two versions running at the same time. Maybe it has something to do with that.

Ross Leishman
Re: Parent/Child Request trying to incombaitibly lock object [message #188214 is a reply to message #188056] Thu, 17 August 2006 08:14 Go to previous message
annagel
Messages: 220
Registered: April 2006
Senior Member
It does do file output via the normal loging, output functionality that is a part if the normal request operation, but so do all the other child requests fired by this parent and these do not result in issues. And if I am not mistaken, the file output it is dealing with directly anyway is indirect in nature, soo all our request does is write data to a table, latter when the concurent manager decides to, which is at the completion of the concurent request, it will actually write this to an out file.

Not sure what you mean by two versions however, the procedures that are the backing code for the requests sit in the same opackage, but they are two seperate entities so each concurent request is running seperate code all of it simply happens to reside in the same package which for some reason this particular child request want to get an exclusive lock to.

This is really what we find strange, all this child request does is optimize a table, so we woudl expect it to want to get an exclusive lock on the table it is generating an index for, but why does it want an exclusive lock on its own package?

Andrew
Previous Topic: DON"T CHANGE DATE
Next Topic: how to upload excel file using pl/sql
Goto Forum:
  


Current Time: Sat Dec 03 18:25:36 CST 2016

Total time taken to generate the page: 0.08924 seconds