SQL Server 是否将 Select...Insert Queries 包装到隐式事务中? [英] Does SQL Server wrap Select...Insert Queries into an implicit transaction?

问题描述

当我执行选择/插入查询时，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?

使用以下查询将值插入表中(如果该值尚不存在):

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')

在 WHERE 子句的评估和 INSERT 子句的执行之间，如果我没有明确地将'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?

推荐答案

您对事务和锁定感到困惑.如果有任何错误，事务会将您的数据恢复到原始状态.如果没有，它会将数据移动到新状态.在处理操作时，您的数据永远不会处于间歇状态.另一方面，锁定是允许或阻止多个用户同时访问数据的锁定.要回答您的问题，select...insert 是原子性的，只要没有明确请求粒度锁，在 select..insert 正在进行时，其他用户将无法插入.

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.

这篇关于SQL Server 是否将 Select...Insert Queries 包装到隐式事务中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！