如何使用数据库管理信号量？

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.

我可能会提出一些可行的SQL命令,它们使用Oracle事务处理,锁存器或其他任何东西,但我宁愿找到一些经过尝试和真实的东西。

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!

几年前,作为SQL wiz的开发人员有一个SQL事务,它获取了信号量,如果得到它则返回true,如果没有得到它则返回false。然后在我处理结束时,我需要运行另一个SQL事务来释放信号量。这很酷,但我不知道数据库支持的信号量是否有可能超时。超时是一个巨大的好处!

EDIT:

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

以下是一些可行的SQL命令,但除了通过cron作业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.
---------------------------------------------------------------------

1 个解决方案

#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.

您应该查看DBMS_LOCK。从本质上讲,它允许Oracle在内部使用的入队锁定机制,除了它允许您定义锁定类型“UL”(用户锁定)。锁可以保持共享或独占,并且锁定或将锁从一种模式转换为另一种模式的请求支持超时。

I think it will do what you want.

我认为它会做你想要的。

Hope that helps.

希望有所帮助。

#1