CODEWITHSUNDEEP
sqloracle-databasestored-procedureslockingjobs
goodDuck
goodDuck

Reputation: 45

How to queue up calls to stored procedures in Oracle?

I have a stored procedure in oracle (which schedules a one-time job to run another procedure, if this is relevant). The job calls another stored procedure which runs for a few minutes, and performs inserts updates and deletes and also uses loops. Now while the long procedure is running, if there is another call for it to run, is it possible to prevent them from executing simultaneously? And even better, to make the second one execute once the previous one has finished, like queue them?

Upvotes: 1

Views: 446

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21095

For your purpose the procedure DBMS_LOCK.ALLOCATE_UNIQUE was designed.

Assign some unique lockname string and call the procedure at the beginning of the critical sequence in your procedure. You will get lockhandle as an output.

Then call DBMS_LOCK.REQUEST to start the unique processing

DBMS_LOCK.ALLOCATE_UNIQUE( v_lockname, v_lockhandle);    
v_res := DBMS_LOCK.REQUEST( lockhandle=>v_lockhandle, release_on_commit => TRUE);

At the end you must release the handle to be able to process the next run

v_res :=  DBMS_LOCK.RELEASE (v_lockhandle);

A good practice is to release it also in the EXCEPTION section to be not blocked after the failure.

Please check the possible options in the documentation such as for release_on_commit and adjust it for your need.

Some care should be taken with the return parameters of the REQUEST and RELEASE procedures.

Upvotes: 2

pifor
pifor

Reputation: 7892

To prevent two stored procedures to run at the same time, you could use DBMS_LOCK to get an exclusive lock (or just try to update the same row in a given table).

Upvotes: 1

Related Questions