[AndyD]

Различие в поведении кроется в использовании для базы данных параметра READ_COMMITTED_SNAPSHOT ON и оптимистической модели конкуренции для DAX2009 (не могу сказать про четверку, ее у меня нет)

В тройке при вставке записи в б/д (при вызове операции insert, а не при подтверждении транзакции) накладывается эксклюзивная (X) блокировка как самой записи, так и соответствующих ей строк индексов. Эта блокировка держится до тех пор, пока не будет завершена транзакция
Когда второй клиент производит чтение из таблицы, то сначала он пытается наложить разделяемую (S) блокировку на ключ индекса, соответствующий искомой записию Но так как первый клиент уже установил X блокировку и не завершил транзакцию, то второй попадает в состояние WAIT и будет ждать завершение первой транзакции.
Как только эта транзакция завершится - второй клиент сможет наложить свою блокировку и увидит уже существующую запись, которую и вернет

Для DAX2009 для первого клиента все пройдет также - создастся запись и наложится X блокировка.
А вот для второго с включенной оптимистической конкуренцией будут существенные отличия. Во-первых, при чтении не будет накладываться S блокировка. Во-вторых, ему не видны незакомиченные на момент начала операции изменения в данных, т.е. select вернет пустой курсор.
Ну а дальше будет попытка сделать вставку с наложением X блокировки, которая, в итоге, будет ожидать окончания первой транзакции. И если она подтвердится, то появится ошибка дубликации данных

Если для операции чтения включить пессимистическую блокировку, то select пойдет на сервер с хинтом updlock, что приведет к попытке наложить X блокировку и последующее ожидание завершения первой транзакции. И возврат существующей записи, если она закоммитится