我正在处理一个应在收到PayPal即时付款通知时创建产品(例如运输保险单)的应用程序。不幸的是，贝宝​​有时会发送重复的通知。此外，还有另一个第三方也在从PayPal获取更新时同时执行Web服务更新。

这是所涉及的数据库表的基本图。

// table "package"
// columns packageID, policyID, other data...
// 
// table "insurancepolicy"
// columns policyID, coverageAmount, other data...

这是我想做的基本图：

using (SqlConnection conn = new SqlConnection(...))
{
  sqlTransaction sqlTrans = conn.BeginTransaction(IsolationLevel.RepeatableRead);

  // Calls a stored procedure that checks if the foreign key in the transaction table has a value.
  if (PackageDB.HasInsurancePolicy(packageID, conn))
  { 
    sqlTrans.Commit();
    return false;
  }

  // Insert row in foreign table.
  int policyID = InsurancePolicyDB.Insert(coverageAmount, conn);
  if (policyID <= 0)
  {
    sqlTrans.Rollback();
    return false;
  }

  // Assign foreign key to parent table.  If this fails, roll back everything.
  bool assigned = PackageDB.AssignPolicyID(packageID, policyID, conn);
  if (!assigned)
  {
    sqlTrans.Rollback();
    return false;
  }
}

如果同时有两个(或者多个)线程(或者进程或者应用程序)在执行此操作，则我希望第一个线程在不包含policyID的情况下锁定" package"行，直到创建策略并分配policyID到包装表。然后，在将policyID分配给程序包表后，将释放该锁。我希望另一个正在调用相同代码的线程在读取包行时暂停，以确保它首先没有policyID。我希望在释放第一个事务的锁时，第二个事务将看到policyID在其中，因此可以在不向策略表中插入任何行的情况下返回。

注意：由于CRUD数据库的设计，每个存储过程都涉及"读取"(选择)，"创建"(插入)或者"更新"。

这是对RepeatableRead事务隔离的正确使用吗？

谢谢。

解决方案

我相信我们实际上是想要Serializable隔离级别。问题是两个线程可以通过HasInsurancePolicyCheck(尽管我不知道InsurancePolicyDB.Insert会做什么或者为什么它将返回0)

我们也有许多其他选择。一种是使用消息队列，然后自己顺序处理这些请求。另一个方法是使用sp_getapplock并锁定该程序包特有的某些键。这样一来，我们不必锁定任何多余的行或者表。

如果"插入Policy"只是在尝试插入重复项时遇到一些唯一性表约束，那会更安全，更清洁。提高隔离级别会降低并发性，并导致其他令人讨厌的问题，如死锁。

另一种方法是始终插入"策略"行，然后在"程序包"已添加到"策略"上时将其回滚：

begin tran (read committed)

/* tentatively insert new Policy */
insert Policy

/* attach Package to Policy if it's still free */
update Package
  set Package.policy_id = @policy_id
  where Package.package_id = @package_id and Package.policy_id is null

if @@rowcount > 0
  commit
else
  rollback

当我们很少遇到冲突时，这种方法最有效。

我同意aaronjensen的回应中的"消息队列"想法。如果我们担心多个并发线程试图同时更新同一行数据，则应该让线程将其数据插入工作队列，然后由一个线程按顺序处理。这大大减少了数据库上的争用，因为目标表仅由一个线程而不是" N"更新，并且工作队列操作限于消息传递线程进行的插入操作以及数据处理线程进行的读取/更新操作。