title: IsolationLevel.RepeatableRead to prevent duplicates
body: <p>I'm working on an application that is supposed to create products (like shipping insurance policies) when PayPal Instant Payment Notifications are received.  Unfortunately, PayPal sometimes sends duplicate notifications.  Furthermore, there is another third-party that is performing web-service updates simultaneously when they get updates from PayPal as well.</p>
  
  <p>Here is a basic diagram of the database tables involved.</p>
  
  <pre><code>// table "package"
  // columns packageID, policyID, other data...
  // 
  // table "insurancepolicy"
  // columns policyID, coverageAmount, other data...
  </code></pre>
  
  <p>Here is a basic diagram of what I want to do:</p>
  
  <pre><code>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 &lt;= 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;
    }
  }
  </code></pre>
  
  <p>If there are two (or more) threads (or processes or applications) doing this at the same time, I want the first thread to lock the "package" row while it has no policyID, until the policy is created and the policyID is assigned to the package table.  Then the lock would be released after the policyID is assigned to the package table.  It is my hope that the other thread which is calling this same code will pause when it reads the package row to make sure it doesn't have a policyID first.  When the first transaction's lock is released, it is my hope that the second transaction will see the policyID is there and therefore return without inserting any rows into the policy table.</p>
  
  <p>Note: Because of the CRUD database design, each the stored procedures involved either Read (select), Create (insert), or Update.</p>
  
  <p>Is this the right use of RepeatableRead transaction isolation?</p>
  
  <p>Thanks.</p>