Is sql statement "where not exists" atomic? No it's not.

/* Short question: is "where not exists" atomic? Short answer: No it will fail, if not in transaction or using locking hint. Even merge will fail. Follow steps to reproduce below. (Written on sql2008r2)
*/ 

/* Preparation script */ 
/*
drop table dbo.Monkey
drop table stage.Monkey
*/ 
create table dbo.Monkey 
( MonkeyId int, 
  MonkeyDate datetime, 
  primary key clustered (Monkeyid))
create table stage.Monkey 
( MonkeyId int identity(1,1), 
  MonkeyDate datetime)
go

set nocount on
insert stage.Monkey 
select getdate()
go 10
insert stage.Monkey 
select getdate() 
from stage.Monkey
go 18
declare @count int = 
( select count(*) 
  from stage.Monkey )

print cast(@count as varchar(10))+' rows in stage.Monkey' -- ~2,5 m rows 


/*
To get constraint violation in dbo.Monkey,
Copy session code below to two different windows.

Execute session 1, then immediately switch to window 2 / session 2 and start that script.
What will happen is that First row (id:1) in session 2 will be inserted while session 1 is in delay.
Second row (id:2000000) will be inserted while insert statement in session 1 is running

Result: Witout explicit x-locking in session 1, session 2 will successfully insert two rows and cause session 1 to fail.
*/ 

/************************************* Session 1 (in this window) **********************************/
--begin tran --this will make session 2 fail
truncate table dbo.Monkey
waitfor delay '00:00:10'

insert dbo.Monkey
select MonkeyId,MonkeyDate 
from stage.Monkey sa 
where not exists 
( select 1 
  from dbo.Monkey a  --with (xlock) -- or enabling this will make session 2 fail 
  where a.MonkeyId=sa.Monkeyid ) 
--commit tran 

/************************************* Session 2 (in new window) **************************/
insert dbo.Monkey 
select 1,'2000-01-01'

waitfor delay '00:00:10'

insert dbo.Monkey 
select 2000000,'2000-01-01' 

--extra corriculum: Merge will follow where not exists and fail without transaction 

/************************************* Session 1b (in this window) **************************/

--begin tran --begin tran will make session 2 fail
truncate table dbo.Monkey
waitfor delay '00:00:10'
merge into dbo.Monkey as target
using 
( 
  select MonkeyId, MonkeyDate 
  from stage.Monkey 
) as source 
  on target.MonkeyId=source.MonkeyId 
when not matched by target then 
 insert (MonkeyId,MonkeyDate) 
 values (source.MonkeyId,source.MonkeyDate);
--commit tran