事务内的 SELECT 语句和事务外的 SELECT 语句有区别吗? [英] Is there a difference between a SELECT statement inside a transaction and one that is outside of it?

问题描述

默认的READ COMMITTED 隔离级别是否以某种方式使SELECT语句在事务中的行为与事务不同一个不在交易中的?

我正在使用 MS SQL.

解决方案

是的，事务内部的人可以看到那个事务中其他先前的Insert/Update/delete语句所做的更改；事务外的 Select 语句不能.

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

而在事务中显式的那个可以(因为它有一个 Begin Transaction 语句)可以在同一个事务中发生其他语句(插入/更新/删除，无论什么)，要么在之前或在 Select 语句之后.

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

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

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

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

• 引擎放置在 Explicit Transaction 模式，当您使用 BEGIN TRANSACTION 语句显式发起交易时.然后，在该事务中执行每个语句，直到您显式终止事务(使用 COMMIT 或 ROLLBACK)，或者发生导致引擎终止和回滚的故障.

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?

I am using MS SQL.

解决方案

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

If all you are asking about is what the Isolation Level does, then understand that all Select statements (hey, all statements of any kind) - are in 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;

whereas the one that is explicitly in 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.

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.

• AutoCommit is 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 Transaction mode, "... 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 Transaction mode when you explicitly initiate a transaction with BEGIN TRANSACTION Statement. Then, every statement is executed within that transaction until you explicitly terminate the transaction (with COMMIT or ROLLBACK) or if a failure occurs that causes the engine to terminate and Rollback.

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