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

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

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

编辑：

以下是一些可行的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.
---------------------------------------------------------------------

解决方法

我认为它会做你想要的.

希望有所帮助.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!