sql - 管理数据库并发性的最佳方式？

Question

我在处理并发时遇到了问题。

在下面的示例中，两个用户 A 和 B 编辑同一张发票并对其进行不同的更改。如果它们都同时单击保存，我希望其中一个成功，另一个失败。否则生成的发票将是不受欢迎的“合并发票”。

这是在 PostgreSQL 中测试的示例(但我认为这个问题应该与数据库无关):

create table invoice (
  id int primary key not null,
  created date
);

create table invoice_line (
  invoice_id int, 
  line numeric(6), 
  amount numeric(10,2),
  constraint fk_invoice foreign key (invoice_id) references invoice(id)
  );

insert into invoice(id, created) values (123, '2018-03-17');
insert into invoice_line (invoice_id, line, amount) values (123, 1, 24);
insert into invoice_line (invoice_id, line, amount) values (123, 2, 26);

所以发票的初始行是:

invoice_id  line  amount
----------  ----  ------
       123     1      24
       123     2      26

现在，用户 A 编辑发票，删除第 2 行并点击“保存”:

-- transaction begins

set transaction isolation level serializable;

select * from invoice where id = 123; -- #1 will it block the other thread?

delete invoice_line where invoice_id = 123 and line = 2;

commit; -- User A would expect the invoice to only include line 1.

同时用户 B 编辑发票并添加第 3 行，然后点击保存:

-- transaction begins

set transaction isolation level serializable;

select * from invoice where id = 123; -- #2 will this wait the other thread?

insert into invoice_line (invoice_id, line, amount) values (123, 3, 45);

commit; -- User B would expect the invoice to include lines 1, 2, and 3.

不幸的是，两个事务都成功了，我得到了合并的行(损坏状态):

invoice_id  line  amount
----------  ----  ------
       123     1      24
       123     3      45

既然这不是我想要的，我有什么选择来控制并发？

Answer 1

这不是数据库并发问题。数据库的 ACID 属性与完成事务有关，同时保持数据库的完整性。在您描述的情况下，交易是正确的，数据库正在正确处理它们。

您需要的是一种锁定机制，本质上是一种信号量，可以保证在任何时候只有一个用户可以对数据进行写访问。您可能能够依赖数据库锁定机制，在锁定失败时进行捕获。

但是，我建议使用其他两种方法中的一种。如果您对仅在应用程序逻辑中进行更改感到满意，则将锁定机制放在那里。有一个用户可以“锁定”表或记录的地方；然后不要让任何人碰它。

您可以更进一步。您可以要求用户获得表的“所有权”以进行更改。然后，您可以实现一个失败的触发器，除非用户是进行更改的人。

而且，您可能会想到其他解决方案。我真正想指出的是，您的用例超出了 RDBMS 默认执行的范围(因为它们会让两个事务都成功完成)。因此，对于任何数据库(我所熟悉的)，您都需要额外的逻辑。