Does SQL Server wrap Select…Insert Queries into an

问题:

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?

回答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.

回答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.

回答3:

A very common problem. Explained here:

Defensive database programming: eliminating IF statements

回答4:

Using SSMS 2016, it can be verified that the Select/Insert statement requests a lock (and so most likely operates atomically):

1. Open a new query/connection for the following transaction and set a break-point on ROLLBACK TRANSACTION before starting the debugger:

   BEGIN TRANSACTION     
   INSERT INTO Table1 (FieldA) VALUES ('newvalue');    
   ROLLBACK TRANSACTION --[break-point]
   

2. While at the above break-point, execute the following from a separate query window to show any locks (may take a few seconds to register any output):

   SELECT * FROM sys.dm_tran_locks
    WHERE resource_database_id = DB_ID()
      AND resource_associated_entity_id = OBJECT_ID(N'dbo.Table1');
   

   There should be a single lock associated to the BEGIN TRANSACTION/INSERT above (since by default runs in an ISOLATION LEVEL of READ COMMITTED)

   OBJECT      **  **********  *   IX  LOCK    GRANT   1   
   

3. From another instance of SSMS, open up a new query and run the following (while still stopped at the above break-point):

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

   This should hang with the string "(Executing)..." being displayed in the tab title of the query window (since @@LOCK_TIMEOUT is -1 by default).

4. Re-run the query from Step 2.

   Another lock corresponding to the Select/Insert should now show:

   OBJECT      **  **********  0   IX  LOCK    GRANT   1
   OBJECT      **  **********  0   IX  LOCK    GRANT   1
   

ref: How to check which locks are held on a table