SQL 事务内的 select 语句和事务外的 select 语句有区别吗

is there a difference between a select statement inside a transaction and one that is outside of it

提问by Omu

Does the default READ COMMITTED isolation level somehow makes the select statement act different inside of a transaction than one that is not in a transaction?

默认的 READ COMMITTED 隔离级别是否以某种方式使 select 语句在事务内部与不在事务中的语句不同？

I am using MSSQL.

我正在使用 MSSQL。

回答by Charles Bretana

Yes, the one inside the transaction can see changes made by other previous Insert/Update/delete statements in thattransaction; a Select statement outside the transaction cannot.

是的，事务内部的人可以看到在其他先前插入/更新/删除语句所做的更改该交易; 事务外的 Select 语句不能。

If all you are asking about is what the Isolation Level does, then understand that all Select statements (hey, all statements of any kind) - arein a transaction. The only difference between one that is explicitly in a transaction and one that is standing on its own is that the one that is standing alone starts its transaction immediately before it executes it, and commits or roll back immediately after it executes;

如果您所问的只是隔离级别的作用，那么请了解所有 Select 语句（嘿，任何类型的所有语句） - 都在事务中。显式在事务中的事务和独立事务的唯一区别在于，单独的事务在执行之前立即启动事务，并在执行后立即提交或回滚；

whereas the one that is explicitlyin a transaction can (because it has a Begin Transaction statement) can have other statements (inserts/updates/deletes, whatever) occurring within that same transaction, either before or after that Select statement.

而显式在事务中的语句（因为它有一个 Begin Transaction 语句）可以在同一事务中发生其他语句（插入/更新/删除等），无论是在 Select 语句之前还是之后。

So whatever the isolation level is set to, both selects (inside or outside an explicit transaction) will nevertheless be in a transaction which is operating at that isolation level.

因此，无论隔离级别设置为什么，两个选择（在显式事务内部或外部）都将处于以该隔离级别运行的事务中。

Addition: The following is for SQL Server, but all databases MUST work in the same way. In SQL Server the Query Processor is always in one of 3 Transaction Modes, AutoCommit, Implicit, or Explicit.

补充：以下是针对 SQL Server 的，但所有数据库必须以相同的方式工作。在 SQL Server 中，查询处理器始终处于 3 种事务模式之一：AutoCommit、Implicit或Explicit。

• AutoCommitis 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. This is the default, and is the answer to @Alex's question in the comments.

• In Implicit Transactionmode, "... the SQL Server Database Engine automatically starts a new transaction after the current transaction is committed or rolled back. You do nothing to delineate the start of a transaction; you only commit or roll back each transaction. Implicit transaction mode generates a continuous chain of transactions. ..." Note that the italicized snippet is for each transaction, whether it be a single or multiple statement transaction.

• The engine is placed in Explicit Transactionmode when you explicitly initiate a transaction with BEGIN TRANSACTIONStatement. Then, every statement is executed within that transaction until you explicitly terminate the transaction (with COMMITor ROLLBACK) or if a failure occurs that causes the engine to terminate and Rollback.

• AutoCommit是 SQL Server 数据库引擎的默认事务管理模式。.. 每个 Transact-SQL 语句在完成时都会提交或回滚。...如果语句成功完成，则提交；如果遇到任何错误，则回滚。这是默认设置，也是评论中@Alex 问题的答案。

• 在隐式事务模式下，“... SQL Server 数据库引擎在当前事务提交或回滚后自动启动一个新事务。您无需执行任何操作来描述事务的开始；您只需提交或回滚每个事务。隐式交易模式会生成一个连续的交易链。...” 注意斜体的片段是针对每个交易的，无论是单条交易还是多条语句交易。

• 当您使用Statement显式启动事务时，引擎将处于显式事务模式BEGIN TRANSACTION。然后，在该事务中执行每个语句，直到您显式终止该事务（使用COMMIT或ROLLBACK）或者如果发生导致引擎终止和回滚的故障。

回答by Jeff Ferland

Yes, there is a bit of a difference. For MySQL, the database doesn't actually start with a snapshot until your first query. Therefore, it's not begin that matters, but the first statement within the transaction. If I do the following:

是的，有一点不同。对于 MySQL，在您第一次查询之前，数据库实际上不会从快照开始。因此，重要的不是开始，而是事务中的第一条语句。如果我执行以下操作：

#Session 1
begin; select * from table;

#Session 2
delete * from table; #implicit autocommit

#Session 1
select * from table;

Then I'll get the same thing in session one both times (the information that was in the table before I deleted it). When I end session one's transaction (commit, begin, or rollback) and check again from that session, the table will show as empty.

然后我将在第一次会话中两次得到相同的信息（删除之前表中的信息）。当我结束会话的事务（提交、开始或回滚）并从该会话再次检查时，该表将显示为空。

回答by Roland Bouman

If your database (or in mysql, the underlying storage engine of all tables used in your select statement) is transactional, then there simply no way to execute it "outside of a transaction".

如果您的数据库（或在 mysql 中，select 语句中使用的所有表的底层存储引擎）是事务性的，那么根本无法在“事务之外”执行它。

Perhaps you meant "run it in autocommit mode", but that is not the same as "not transactional". In the latter case, it still runs in a transaction, it's just that the transaction ends immediately after your statement is finshed.

也许您的意思是“在自动提交模式下运行它”，但这与“非事务性”不同。在后一种情况下，它仍然在事务中运行，只是在您的语句完成后事务立即结束。

So, in both cases, during the run, a single select statement will be isolated at the READ COMMITTED level from the other transactions.

因此，在这两种情况下，在运行期间，单个 select 语句将在 READ COMMITTED 级别与其他事务隔离。

Now what this means for your READ COMMITTED transaction isolation level: perhaps surprisingly, not that much.

现在这对您的 READ COMMITTED 事务隔离级别意味着什么：也许令人惊讶的是，并没有那么多。

READ COMMITTED means that you may encounter non-repeatable reads: when running multiple select statements in the same transaction, it is possible that rows that you selected at a certain point in time are modified and comitted by another transaction. You will be able to see those changes when you re-execute the select statement later on in the same pending transaction. In autocommit mode, those 2 select statements would be executed in their own transaction. If another transaction would have modified and committed the rows you selected the first time, you would be able to see those changes just as well when you executed the statement the second time.

READ COMMITTED 意味着您可能会遇到不可重复读：在同一个事务中运行多个 select 语句时，您在某个时间点选择的行可能被另一个事务修改并提交。当您稍后在同一挂起事务中重新执行 select 语句时，您将能够看到这些更改。在自动提交模式下，这 2 个选择语句将在它们自己的事务中执行。如果另一个事务修改并提交了您第一次选择的行，那么您在第二次执行该语句时也能看到这些更改。

回答by Gabriel McAdams

The READ COMMITTED isolation level is about the records that have been written. It has nothing to do with whether or not this select statement is in a transaction (except for those things written during that same transaction).

READ COMMITTED 隔离级别与已写入的记录有关。这与这个 select 语句是否在一个事务中无关（除了在同一个事务中编写的那些东西）。