How to use a database to control semaphore?

Question

If multiple instances of the same code are running on different servers, I would like to use the database to make sure that the process does not start on one server if it is already running on another server.

Perhaps I could come up with some working SQL commands that used Oracle transaction processing, latches, or something else, but I would rather find something that has been verified and true.

A few years ago, an SQL developer had a single SQL transaction that took up a semaphore and returned true if it was received, and returned false if it did not receive it. Then, at the end of my processing, I will need to run another SQL transaction to release the semaphore. It would be great, but I do not know if it is possible for a semaphore with database support to time out. That would be a huge bonus to have a timeout!

EDIT:

Here is what might be some workable SQL commands, but without a timeout, except as a result of cron:

--------------------------------------------------------------------- --Setup --------------------------------------------------------------------- CREATE TABLE "JOB_LOCKER" ( "JOB_NAME" VARCHAR2(128 BYTE), "LOCKED" VARCHAR2(1 BYTE), "UPDATE_TIME" TIMESTAMP (6) ); CREATE UNIQUE INDEX "JOB_LOCKER_PK" ON "JOB_LOCKER" ("JOB_NAME") ; ALTER TABLE "JOB_LOCKER" ADD CONSTRAINT "JOB_LOCKER_PK" PRIMARY KEY ("JOB_NAME"); ALTER TABLE "JOB_LOCKER" MODIFY ("JOB_NAME" NOT NULL ENABLE); ALTER TABLE "JOB_LOCKER" MODIFY ("LOCKED" NOT NULL ENABLE); insert into job_locker (job_name, locked) values ('myjob','N'); commit; --------------------------------------------------------------------- --Execute at the beginning of the job --AUTOCOMMIT MUST BE OFF! --------------------------------------------------------------------- select * from job_locker where job_name='myjob' and locked = 'N' for update NOWAIT; --returns one record if it ok. Otherwise returns ORA-00054. Any other thread attempting to get the record gets ORA-00054. update job_locker set locked = 'Y', update_time = sysdate where job_name = 'myjob'; --1 rows updated. Any other thread attempting to get the record gets ORA-00054. commit; --Any other thread attempting to get the record with locked = 'N' gets zero results. --You could have code to pull for that job name and locked = 'Y' and if still zero results, add the record. --------------------------------------------------------------------- --Execute at the end of the job --------------------------------------------------------------------- update job_locker set locked = 'N', update_time = sysdate where job_name = 'myjob'; --Any other thread attempting to get the record with locked = 'N' gets no results. commit; --One record returned to any other thread attempting to get the record with locked = 'N'. --------------------------------------------------------------------- --If the above 'end of the job' fails to run (system crash, etc) --The 'locked' entry would need to be changed from 'Y' to 'N' manually --You could have a periodic job to look for old timestamps and locked='Y' --to clear those. --------------------------------------------------------------------- 

Answer 1

You should look in DBMS_LOCK. Essentially, it allows the queue blocking mechanisms that Oracle uses internally, except that it allows you to specify the type of blocking that is “UL” (user blocking). Locks can be divided or exclusive, and a request for a lock or lock conversion from one mode to another supports a timeout.

I think he will do what you want.

Hope this helps.