SQL Server 事务和 SELECT 语句 [英] SQL Server transaction and SELECT statement

问题描述

我经常看到很多人在事务中使用 SELECT 语句.我经常只在事务中使用 insert/update/delete.我只是不明白在事务中放置 SELECT 语句有什么用处.

I often saw many people use SELECT statement within a transaction. I often use insert/update/delete only in transaction. I just do not understand that what is the utility of putting a SELECT statement inside transaction.

我得到了一个答案....SELECT 事务内部可以看到该事务中其他先前的 Insert/Update/Delete 语句所做的更改，一个 SELECT 语句不能.

I got one answer that....SELECT inside the transaction can see changes made by other previous Insert/Update/Delete statements in that transaction, a SELECT statement outside the transaction cannot.

以上说法是真是假?

这是人们将 SELECT 语句放入事务中的唯一原因吗?如果可能，请详细讨论所有原因.谢谢

Is this is the only reason that people put SELECT statement inside transaction? Please discuss all the reason in detail if possible. thanks

推荐答案

尝试这样做，你就会明白:

try doing this and you will understand:

在 SSMS 上打开两个新查询(从现在起称为 A 和 B)，然后在 A 上创建一个简单的表，如下所示:

Open a two new queries on SSMS (lets call it A and B from now one) and on A, create a simple table like this:

create table transTest(id int)
insert into transTest values(1)

现在，请执行以下操作:

now, do the following:

do select * from transTest 在两者中.你会看到值 1

do select * from transTest in both of them. You will see the value 1

运行时:

set transaction isolation level read committed

在 B 运行:

begin transaction
insert into transTest values(2)

运行时:

select * from transTest

您将看到查询无法完成，因为它被 A 上的事务锁定

you will see that the query wont finish because it is locked by the transaction on A

在 B 运行:

commit transaction

回到A，你会看到查询完成

Go back to A and you will see that the query finished

重复测试设置事务隔离级别读取未提交 on A你会看到查询不会被交易锁定

Repeat the test with set transaction isolation level read uncommitted on A you will see that the query wont be locked by the transaction

这篇关于SQL Server 事务和 SELECT 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！