Workflow and procedure locking?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Workflow and procedure locking?

  1. #1
    Join Date
    Jun 2000
    Posts
    417
    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?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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:
    Code:
    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:

    Code:
    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')
    /
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width