Oracle 10g：有什么好的学术方法可以防止记录连续更新？

Question

我们有一个名为“合同”的表。这些合同记录由外部站点上的用户创建，并且必须由内部站点上的工作人员批准或拒绝。当合同被拒绝时，它只是从数据库中删除。然而，当它被接受时，会生成一条称为“合同接受”的新记录，该记录会写入自己的表中，并从合同中存在的数据中派生出来。

问题是两名内部员工最终可能会签订同一份合同。第一个用户接受并生成合同接受记录。然后，在页面上仍打开相同的合同记录的情况下，第二个用户再次接受合同，从而创建重复的接受记录。

解决这个问题的快速而肮脏的方法是在合约被接受之前从数据库检索合约，检查状态，并生成一条错误消息，表明它已被接受。这可能适用于大多数情况，但用户仍然可以同时单击“接受”按钮并偷偷通过此验证代码。

我还考虑过在数据层深处使用线程锁，以防止两个线程同时进入同一代码区域，但该应用程序存在于两个负载平衡的服务器上，因此用户可能位于不同的服务器上，这将导致使这种方法毫无用处。

我能想到的唯一方法必须存在于数据库中。从概念上讲，我想以某种方式锁定存储过程或表，以便它不能同时更新两次，但也许我在这里对 Oracle 的了解不够。更新如何进行？更新请求是否以某种方式排队，以便它们不会在完全相同的时间发生？如果是这样，我可以检查 SQL 中记录的状态，并在输出参数中返回一个值，表明该记录已被接受。但是，如果更新请求没有排队，那么两个人仍然可以同时进入更新 sql。

寻找有关如何解决此问题的好建议。

Answer 1

首先，如果每个合同只能有一个合同接受，那么合同接受应该将合同 ID 作为其自己的主（或唯一）键：这将使重复成为不可能。

其次，为了防止第二个用户在第一个用户接受合同时尝试接受合同，您可以使接受过程锁定合同行：

select ...
from Contract
where contract_id = :the_contract
for update nowait;

insert into Contract_Acceptance ...

第二个用户的接受尝试将失败并出现异常：

ORA-00054: resource busy and acquire with nowait specified

Answer 2

一般来说，有两种解决问题的方法

选项 1：悲观锁定

在这种情况下，您是悲观的，因此在选择表中的行时将其锁定。当用户查询 Contracts 表时，他们会执行类似“

SELECT *
  FROM contracts
 WHERE contract_id = <<some contract ID>>
   FOR UPDATE NOWAIT;

无论谁先选择记录，都将锁定它”之类的操作。无论是谁第二次选择该记录，都会收到 ORA-00054 错误，应用程序随后会捕获该错误，并让他们知道另一个用户已经锁定了该记录。当第一个用户完成他们的工作时，他们将 INSERT 发出到 Contract_Acceptance 表中并提交他们的事务。这将释放 Contracts 表中行的锁定。

选项 2：乐观锁定

在这种情况下，您乐观地认为两个用户不会发生冲突，因此您最初不会锁定记录。相反，您可以选择所需的数据以及添加到表中的 Last_Updated_Timestamp 列（如果该列尚不存在）。当

SELECT <<list of columns>>, Last_Updated_Timestamp
  FROM Contracts
 WHERE contract_id = <<some contract ID>>

用户接受合同时，在将 INSERT 插入 Contract_Acceptance 之前，他们会对合同发出 UPDATE

UPDATE Contracts
   SET last_updated_timestamp = systimestamp
 WHERE contract_id = <<some contract ID>>
   AND last_update_timestamp = <<timestamp from the initial SELECT>>;

第一个执行此更新的人将成功（该语句将更新 1 行）。第二个人执行此操作将更新 0 行。应用程序检测到更新没有修改任何行，并告诉第二个用户其他人已经处理了该行。

无论哪种情况

无论哪种情况，您可能都希望向 Contract_Acceptance 表添加 UNIQUE 约束。这将确保对于任何给定的 Contract_ID，Contract_Acceptance 表中只有一行。

ALTER TABLE Contract_Acceptance
  ADD CONSTRAINT unique_contract_id UNIQUE (Contract_ID)

这是第二道防线，永远不需要，但可以在应用程序未正确实现其逻辑的情况下保护您。