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?
Advertisement
Answer
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).