sql - 防止重复预订

Question

假设我有以下内容:

create table jobs
(
  staff_id number,
  job_name varchar2(1000) not null,
  start_date date not null,
  end_date date not null
);

这只是一张表格，列出了员工的各种工作。

现在员工一次只能做一项工作，所以我使用以下 PL/SQL 语句插入工作。如果发现冲突作业，则不会添加任何作业(我可能应该在此处报告错误，但对于这个简化的示例，我忽略了它):

create or replace procedure add_job
(
  p_staff_id number, 
  p_job_name varchar2, 
  p_start_date date, 
  p_end_date date
) 
as
begin
  insert into jobs 
  (
    select p_staff_id, p_job_name, p_start_date, p_end_date from dual 
    where not exists
    (
      select 1 from jobs 
      where staff_id = p_staff_id
        and (end_date > p_start_date)
        and (start_date < p_end_date)
    )
  );
end;

问题是，如果我在两个不同的 session 中添加两个不同的工作，然后提交，我可以加倍预订。即以下内容:

-- Session 1:
add_job(1, 'Help Alice', to_date('2011/08/21 11:00:00', 'YYYY/MM/DD HH24/MI/SS'), to_date('2011/08/21 13:00:00', 'YYYY/MM/DD HH24/MI/SS'));

-- Session 2:
add_job(1, 'Help Bob',   to_date('2011/08/21 12:00:00', 'YYYY/MM/DD HH24/MI/SS'), to_date('2011/08/21 14:00:00', 'YYYY/MM/DD HH24/MI/SS'));

-- Session 1:
commit;

-- Session 2:
commit;

上面，staff_id 1 将在 12:00 到 13:00 之间重复预订。

似乎添加到我程序的开始:

lock table jobs in exclusive mode;

成功了，我觉得这个锁太宽泛了。有什么办法可以强制 oracle 做一些更细粒度的事情。如果可能的话，我宁愿不要乱用 dbms_lock。这page提示 select ... for update 可以解决问题，但没有提供详细信息。

如果没有全表锁或 dbms_lock 锁，有什么方法可以阻止重复预订的发生？ (如果有区别的话，我正在使用 Oracle 10g)。

Answer 1

您显然需要一把锁来防止重复预订。由于您需要锁定特定的职员，我的建议是在插入语句之前锁定职员中的一行:

select id from staff where id = p_staff_id for update;

这样锁只会影响一名工作人员(前提是您有行级锁)。