sql - 选择适当的锁定 :

Question

假设我有一个表 T，其中包含以下列:

create table T as
(
  supplier varchar2,
  item varchar2,
  price number,
  is_best_price number,
);

我们有一个插入项目的过程:

create or replace procedure insert_item
  (p_supplier as varchar2, p_item as varchar2, p_price as number) as
declare
  v_best_price;
  v_is_best_price number;
begin
  select min(price) into v_best_price from T where item = p_item;

  if (v_best_price is null) then
    v_is_best_price := 1;
  elsif (price <= v_best_price) then
    v_is_best_price := 1;
  else
    v_is_best_price := 0;
  end if;

  if (v_is_best_price = 0) then
    update T set is_best_price = 0 where item = p_item and is_best_price = 1;
  end if;

  insert into T values (p_supplier, p_item, p_price, v_is_best_price);
end;

这里的不变量是

对于所有行 x:(x.v_best_price = 1) iff (x.v_price = select min(price) from T where item = x.item)

或者用简单的英语来说，如果商品的价格是最优惠的价格，is_best_price 就是 1。

如果我在两个不同的 session 中执行此操作，则会出现问题:

insert_item('alice', 'pants', '30');
insert_item('bob', 'pants', '20');

现在，如果我理解正确，可能会发生以下情况:

(1) 执行insert_item('alice', 'pants', '30')(线程A)
(2) 执行insert_item('bob', 'pants', '20')(线程B)
(3)线程A查询表，发现没有其他裤子，于是设置v_is_best_price := 1。
(4) 线程 B 查询表，注意到没有其他裤子(因为线程 A 尚未插入)，因此设置 v_is_best_price := 1。
(5) 线程A确实插入了('alice', 'pants', '30', 1).
(6) 线程 B 确实插入了 ('bob', 'pants', '20', 1)。

我们违反了不变量。

所以我意识到我可以通过执行以下操作在选择之前的过程的第一行锁定整个表:

lock table T in exclusive mode;

如果我理解正确的话，这意味着在线程 A 完成之前，对表的任何读取或写入都将停止(即线程 A 和线程 B 无法并行运行)。

除了锁定整个表之外，还有其他方法可以做到这一点吗？ select ... for update 有帮助吗？或者有没有其他方法可以进行更精细的粒度锁定？

如果这有什么不同的话，我正在使用 Oracle 10g。

Answer 1

是的，这是一个竞争条件，允许两个条目都认为他们有最好的价格。您要么必须想出一个复杂的场景来使用唯一索引(在这种情况下，第二次插入将在提交时失败)，要么重新考虑您是否应该存储此标志。

虽然它是一个很好的标志并且很方便，但它也有点错误。

想象一下，我插入一行以降低商品的现有价格，代码块中的逻辑不会将其他行的 is_best_price 设置为 0 - 它只是决定在添加的行上做什么，所以我无论竞争条件如何，同一项目都可以得到两行 1。

如果您负担得起处理开销，请在需要时即时计算哪个是最好的，而不是在进行过程中试图维护这些值。