IsolationLevel.RepeatableRead to prevent duplicates

// 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;
  }
}

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