Is "REPEATABLE READ" / "SERIALIZABLE" required for INSERT ... SELECT ... WHERE NOT EXISTS (...) ?
Posted on 2008-06-24
I have multiple threads at the very same time trying to insert the same record to one table (say TableA) like this:
INSERT INTO TableA (Col1)
SELECT TOP 1 B.Col1 FROM TableB B
WHERE NOT EXISTS (SELECT * FROM TableA A WHERE A.Col1 = B.Col1)
Currently, I use the default isolation level (READ COMMITTED) and found that more than 1 threads are able to insert the same record from TableB into TableA, why?
Do I need to use REPEATABLE READ or SERIALIZABLE instead?
Thank you very much,