Does SQL Server wrap Select...Insert Queries into an implicit transaction?

Question

When I perform a select/Insert query, does SQL Server automatically create an implicit transaction and thus treat it as one atomic operation?

Take the following query that inserts a value into a table if it isn't already there:

INSERT INTO Table1 (FieldA)
SELECT 'newvalue' 
WHERE NOT EXISTS (Select * FROM Table1 where FieldA='newvalue')

Is there any possibility of 'newvalue' being inserted into the table by another user between the evaluation of the WHERE clause and the execution of the INSERT clause if I it isn't explicitly wrapped in a transaction?

Answer 1

You are confusing between transaction and locking. Transaction reverts your data back to the original state if there is any error. If not, it will move the data to the new state. You will never ever have your data in an intermittent state when the operations are transacted. On the other hand, locking is the one that allows or prevents multiple users from accessing the data simultaneously. To answer your question, select...insert is atomic and as long as no granular locks are explicitly requested, no other user will be able to insert while select..insert is in progress.

Answer 2

John, the answer to this depends on your current isolation level. If you're set to READ UNCOMMITTED you could be looking for trouble, but with a higher isolation level, you should not get additional records in the table between the select and insert. With a READ COMMITTED (the default), REPEATABLE READ, or SERIALIZABLE isolation level, you should be covered.

Answer 3

A very common problem. Explained here:

Defensive database programming: eliminating IF statements