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(... ... );
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.
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.