SQL Server包装选择...将查询插入隐式事务吗？

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

当我执行select / Insert查询时,SQL Server是否会自动创建一个隐式事务,从而将其视为一个原子操作?

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?

如果我没有在事务中明确地包装它,那么在WHERE子句的评估和INSERT子句的执行之间是否有可能由另一个用户将“newvalue”插入到表中?

4 个解决方案

#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.

您在事务和锁定之间感到困惑。如果有任何错误,事务会将您的数据恢复为原始状态。如果不是,它会将数据移动到新状态。在操作交易时,您永远不会将数据置于间歇状态。另一方面,锁定是允许或阻止多个用户同时访问数据的锁定。要回答您的问题,请选择... insert is atomic,只要没有显式请求粒度锁,当select..insert正在进行时,其他用户将无法插入。

#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.

约翰,答案取决于你目前的隔离级别。如果您设置为READ UNCOMMITTED,您可能正在寻找麻烦,但是如果隔离级别较高,则不应在select和insert之间的表中获得其他记录。使用READ COMMITTED(默认值),REPEATABLE READ或SERIALIZABLE隔离级别,您应该受到保护。

#3

A very common problem. Explained here:

一个非常常见的问题。在这里解释:

Defensive database programming: eliminating IF statements

防御性数据库编程:消除IF语句

#4

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

使用SSMS 2016,可以验证Select / Insert语句是否请求锁定(因此最有可能以原子方式运行):

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

为以下事务打开一个新的查询/连接,并在启动调试器之前在ROLLBACK TRANSACTION上设置一个断点:
```
BEGIN TRANSACTION     
INSERT INTO Table1 (FieldA) VALUES ('newvalue');    
ROLLBACK TRANSACTION --[break-point]
```
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)

应该有一个与上面的BEGIN TRANSACTION / INSERT关联的锁(因为默认情况下运行在READ COMMITTED的ISOLATION LEVEL中)
```
OBJECT      **  **********  *   IX  LOCK    GRANT   1   
```
From another instance of SSMS, open up a new query and run the following (while still stopped at the above break-point):

从SSMS的另一个实例中,打开一个新查询并运行以下命令(同时仍在上述断点处停止):
```
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).

这应该挂起,并在查询窗口的标签标题中显示字符串“(Executing)...”(因为@@ LOCK_TIMEOUT默认为-1)。
Re-run the query from Step 2.

从第2步重新运行查询。

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

现在应该显示对应于Select / Insert的另一个锁:
```
OBJECT      **  **********  0   IX  LOCK    GRANT   1
OBJECT      **  **********  0   IX  LOCK    GRANT   1
```

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

ref:如何检查表上保存的锁

#1

#2

#3