Workflow and procedure locking?

[pwoneill]

We're having a reoccuring problem with a pl/sql package becoming locked, however there is no trace of anything in the database. Since we can't find a session or anything holding the lock, the only solution we could find is to bounce it. I've looked at it, and two of our oracle consultants looked at it and nobody could find what was making the lock, or any way to free it.

We notice the problem when developers have to compile new changes into the procedure. They attempt to compile, and it times out waiting for locked object packagename.

The package is used in Oracle Workflow, so every time a workflow of a specific item type is created, all the code is executed from this package.

I have a feeling I know what the problem is, and suggested the fix to the developer but it hasn't been implemented yet due to various reasons.

One of the steps in the workflow is to send a message out to some external source, and wait for a response.

What his code does, is create an internal loop in the procedure which polls for the response, sleeps, polls for the response, sleeps, etc. So the procedure is constantly running, waiting for a response. Any other workflows which are instantiated would do the same thing.

I believe that because the procedures are constantly running this is what's causing the problem. The procedure is locked from modification because instances of the workflows are constantly accessing it.

My suggestion was just to make the loop a workflow loop. The procedure would simply check once to see if there was a message, then termainate. If nothing was found, workflow would wait for whatever duration, then run the procedure to check again. Personally I feel this is a better design, and would resolve the problem.

Does that sound correct? If not, any other ideas about what could be locking this package?

[marist89]

Sounds like you are most likely hitting a DDL locking problem.

You may have already looked at this, but you can get the locks for a particular user with a script similar to:

col "CLIENT/PID" for a20
col owner for a12
col username for a12
set linesize 132
select l.session_id, s.serial#, l.owner, s.username,
   decode(s.process, NULL, s.machine, s.machine || ' PID: ' 
      ||  to_char(s.process)) "CLIENT/PID" ,
   s.server, s.status
from dba_ddl_locks l, v$session s
where s.sid = l.session_id
and l.name = upper('&1')
/

Also, if you think you know the package that is causing the problem, you can see who also holds a lock on that package with a script similar to:

col username for a12
col object_locked for a40
col MACHINE/PID for a30
col program for a30
set linesize 132
set verify off
accept objowner prompt 'Object Owner: '
accept objname prompt 'Object Name : '

select s.username, s.sid, s.program, d.owner || '.' || d.name object_locked,
decode(s.process, NULL, s.machine, s.machine || ' PID: ' ||  to_char(s.process)) "MACHINE/PID"
from v$session s, dba_ddl_locks d
where s.sid = d.session_id
and d.type like '%/Procedure/%'
and d.owner = upper('&objowner')
and d.name = upper('&objname')
/