USE TestDatabase

-- Create this table once
-- create table ConcurrencyTest (id int primary key, value int)
-- truncate table ConcurrencyTest

-- Run this in two windows
SET xact_abort ON
while 1=1
    BEGIN

    DECLARE @newid INT

    SELECT  @newid = isnull(MAX(id),0) + 1
    FROM    ConcurrencyTest


    MERGE INTO ConcurrencyTest ct
    USING (
       SELECT @newid AS new_id
    ) t ON ct.id = t.new_id
    WHEN NOT matched THEN
      INSERT (id) VALUES (t.new_id);

      END

-- You should get a primary key violation in one of the window