We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error.
Whats the best method of doing this?
lockingoracleoracle-11g-r2plsql
We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error.
Whats the best method of doing this?
Best Answer
You can do this with
DBMS_LOCK
and an exclusive lock.See the following procedure:
Test (session 1):
(Obviously returns when
DBMS_LOCK.sleep()
returns).Test (session 2):
Obviously you need to
GRANT EXECUTE ON DBMS_LOCK TO YOURUSER;
.