SQL Server Making insert select where not exists atomic

Asked Apr 14 '17 at 18:35

Active Apr 14 '17 at 18:35

Viewed 732 times

I am aware that INSERT ... SELECT .. WHERE NOT EXISTS (SELECT...) is not atomic.

There is a race condition where two sessions running concurrently can run the select; both see the row does not exist and then both would attempt the insert.

How would you make this statement atomic in SQL Server? I am not sure which locking hint to use or where to put it.

asked Apr 14 '17 at 18:35

wrschneider

16,836
14
84
162

2

Put it in a transaction? – dfundako Apr 14 '17 at 18:40
http://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx – S3S Apr 14 '17 at 18:54

SQL Server Making insert select where not exists atomic

0 Answers0