SQL Server事务和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可以看到该事务中其他先前的Insert / Update / Delete语句所做的更改，事务外的SELECT语句不能。

Above statement is it true or not ?

以上陈述是真的与否？

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

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

2 个解决方案

#1

try doing this and you will understand:

尝试这样做，你会明白：

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:

在SSMS上打开两个新查询（从现在开始称之为A和B），在A上创建一个如下所示的简单表：

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

now, do the following:

现在，执行以下操作：

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

从transTest中选择*。您将看到值1

On A run:

在奔跑：

set transaction isolation level read committed

On B run:

在B跑：

begin transaction
insert into transTest values(2)

On A run:

在奔跑：

select * from transTest

从transTest中选择*

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

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

On B run:

在B跑：

commit transaction

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

回到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

重复测试，设置事务隔离级别读取未提交在A上，您将看到该查询不会被事务锁定

#2

One of the main reasons I can think of (the only reason, in fact) is if you want to set a different isolation level, eg:

我能想到的主要原因之一（事实上唯一的原因）是你想设置一个不同的隔离级别，例如：

USE AdventureWorks2008R2;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;

SELECT * FROM HumanResources.EmployeePayHistory;

SELECT * FROM HumanResources.Department;

COMMIT TRANSACTION;

For single SELECT statements though, I'm not so sure, unless you had a reason to go the other way and set READ UNCOMMITTED in cases where response time/maximising concurrency is more important than accurate or valid data.

但是对于单个SELECT语句，我不太确定，除非你有理由采用其他方式并在响应时间/最大化并发比准确或有效数据更重要的情况下设置READ UNCOMMITTED。

<speculation certainty="75%"> If the single SELECT statement is inside an explicit transaction without altering the isolation levels, I'm pretty sure that will have no effect at all. Individual statements are, by themselves, transactions that are auto-committed or rolled back on error.</speculation>

如果单个SELECT语句在显式事务中而不改变隔离级别，我很确定它根本不起作用。单个语句本身就是自动提交或在出错时回滚的事务。