title: Avoid Violation of PRIMARY KEY in delete\insert transaction?
body: <p>I have pretty simple T-SQL query</p>
  <pre><code>DELETE FROM my_table
  WHERE [id] = @Id
  
  INSERT INTO my_table
      ([id], [payload])
  VALUES
      (@Id, @Payload)
  </code></pre>
  <p>A lot of these queries executing in different threads. INSERT and DELETE are in <strong>transaction</strong> with type <strong>Read Commited</strong>. Everything works good 99.9% of time, but there is an edge case when it fails with:</p>
  <pre><code>Violation of PRIMARY KEY constraint 'PK_my_table'. Cannot insert duplicate key in object 'dbo.my_table'. The duplicate key value is (9).
  The statement has been terminated.
  </code></pre>
  <p>The problem appears when:</p>
  <ol>
  <li>We have 2 transactions with the same @Id</li>
  <li>We don't have a record with @Id in the table my_table</li>
  </ol>
  <p>So the 1st and the 2nd transactions start with DELETE. Both transactions delete nothing because there wasn't record in the table my_table. Both start to INSERT and BOOM - Violation of PRIMARY KEY.</p>
  <p>The question is how to avoid this case with <strong>Read Commited transaction type</strong> and <strong>without using MERGE statement</strong>?</p>