3

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?

GEOCHET
  • 21,119
  • 15
  • 74
  • 98
JohnFx
  • 34,542
  • 18
  • 104
  • 162
  • Is (or why not make) FieldA a key field with the unique property? Seems like a good way to avoit duplicates, which seems like what you are looking for. – JonnyBoats Jul 01 '09 at 20:49
  • Can't go into the details, but for complicated reasons I can't directly add constraints to the DB (hence my workaraound with this type of query). – JohnFx Jul 01 '09 at 21:26
  • Okay.. Just so I don't look too braindead here. I'll give a LITTLE background. The DB is controlled/owned by a 3rd party product which I am querying through their API that builds the actual SQL that is executed. It doesn't have support for transactions and modifying the objects their database probably would void the warranty. =) – JohnFx Jul 01 '09 at 21:39
  • A telling indicator that the Select/Insert operation is probably atomic is that a break point includes the entire statement. – samus Apr 24 '18 at 15:05
  • [Autocommit mode](https://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx) is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. A connection to an instance of the Database Engine operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions. Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library. – samus Apr 24 '18 at 20:16

3 Answers3

4

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.

msvcyc
  • 2,569
  • 4
  • 24
  • 30
  • Not so much. I do understand the difference. The reason I mentioned transactions is mainly because that seems to be the key mechanism in SQL server uses for designating critical sections/locks. In any case, thanks for your answer. – JohnFx Jul 01 '09 at 21:34
  • *"You will never ever have your data in an intermittent state when the operations are transacted."* - If dirty reads are allowed by an isolation level of *READ UNCOMMITTED*, then a modified record that is rolled back can make it's way back into the DB if subsequently written by whatever process performed the dirty read, which technically would be an intermittent state. – samus Apr 23 '18 at 19:14
  • Transactions and locks are like cookies and milk - *"[Transactions](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017) running at the **READ UNCOMMITTED** level do not issue **shared locks** to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions."* – samus Apr 23 '18 at 20:52
3

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.

Scott Ivey
  • 40,768
  • 21
  • 80
  • 118
  • I'm assuming that the default (READ COMMITTED) is used in this situation since this uses an implicit transaction and not an explicit one. That sound right? – JohnFx Jul 01 '09 at 21:33
  • yes - isolation level is separate from the transactions, so will be the same unless you change it with lock hints or by setting it explicitly. – Scott Ivey Jul 02 '09 at 00:04
1

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

samus
  • 6,102
  • 6
  • 31
  • 69