How do I use a database to manage a semaphore?

Question

If several instances of the same code are running on different servers, I would like to use a database to make sure a process doesn't start on one server if it's already running on another server.

I could probably come-up with some workable SQL commands that used Oracle transaction processing, latches, or whatever, but I'd rather find something that's been tried and true.

Years ago a developer that was a SQL wiz had a single SQL transaction that took the semaphore and returned true if it got it, and returned false if it didn't get it. Then at the end of my processing, I'd need to run another SQL transaction to release the semaphore. It would be cool, but I don't know if it's possible for a database-supported semaphore to have a time-out. That would be a huge bonus to have a timeout!

EDIT:

Here are what might be some workable SQL commands, but no timeout except through a cron job hack:

---------------------------------------------------------------------
--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's 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 into DBMS_LOCK. Essentially, it allows for the enqueue locking mechanisms that Oracle uses internally, except that it allows you to define a lock type of 'UL' (user lock). Locks can be held shared or exclusive, and a request to take a lock, or to convert a lock from one mode to another, support a timeout.

I think it will do what you want.

Hope that helps.