Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    5

    Answered: DBMS_LOCK.SLEEP locking

    Hello Forum,

    i use DBMS_LOCK.SLEEP(14) procedure proc1 to repeat execution of set of statement in a loop within 14 sec. each.
    loop have logic:
    if condition1 is true Then
    delete table1 where col1 > variable;
    insert into table1 values(... ... );
    End If;

    The proc1 runs every minute implementing some kind of queue.
    What I noticed, that when I try to create or drop index on table1, i am getting message that table1 is locked.

    condition1 is true quite rarely but table1 is always locked.

    Here are my questions:
    1. I do not want table1 to be locked unless it runs delete stm.
    2. If you think dbms_lock is causing exclusive locking, can it be avoided?
    3. If I create another proc2 which contains delete and insert rows and call the proc2 instead of having delete & insert in proc1, will it eliminate the issue.
    4. i could move 'sleeping' logic to .sh script, but for some management issues it is the least desirable option.

  2. Best Answer
    Posted by Littlefoot

    "It is not DBMS_LOCK that causes a table to be locked - it is a DELETE (and INSERT) which did that. If you committed these changes, i.e. modified your code to
    Code:
    if condition1 is true then
       delete ...
       insert ...
       COMMIT;
    end if
    then another session wouldn't see the table locked.

    Furthermore, I'd use a job instead of DBMS_LOCK. Have a look at DBMS_JOB and/or DBMS_SCHEDULER. If certain code has to be repeated, then schedule it."


  3. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It is not DBMS_LOCK that causes a table to be locked - it is a DELETE (and INSERT) which did that. If you committed these changes, i.e. modified your code to
    Code:
    if condition1 is true then
       delete ...
       insert ...
       COMMIT;
    end if
    then another session wouldn't see the table locked.

    Furthermore, I'd use a job instead of DBMS_LOCK. Have a look at DBMS_JOB and/or DBMS_SCHEDULER. If certain code has to be repeated, then schedule it.

  4. #3
    Join Date
    Aug 2008
    Posts
    5
    Thank you for your answer.
    I just checked the code of my proc1. It does have 'commit' as the 3rd and last statement in if / end if .

    I will double check again with dba what he is using to submit execution of the proc.
    I will also explore DBMS_JOB facility following your recommendation.

    Thank you, Gene.

  5. #4
    Join Date
    Aug 2008
    Posts
    5
    Littlefoot, thank you again for your help. I was breaking my head and nobody in a team had explanation.
    After checking the code, I found 2 places where in proc1 were insert statements into table1.
    After I added commit in both places, I did not see exclusive lock holding during 1 min. of execution.
    I also tested it by creating and dropping index.

    Your recommendation was exact right.

Posting Permissions

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