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?
默认的读提交隔离级别是否以某种方式使select语句在事务中与不在事务中的语句不同?
I am using MSSQL.
我使用该软件。
4 个解决方案
#1
55
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....
是的,里面的一个事务可以看到更改其他先前的插入/更新/删除语句的事务,一个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), - are in a transaction. The only difference between one that is explicitly in a transaction and one that is standing on it's own is that the one that is standing alone starts it's transaction immediately before it executes it, and commits or roll back immediately after it executes,
如果您要问的是隔离级别的作用，那么要理解——所有的Select语句(嘿，所有的语句)都在事务中。一个在事务中显式的唯一区别是独立存在的唯一的区别是在执行它之前独立启动它的事务，在它执行之后立即提交或回滚，
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) occcurring within that same transaction, either before or after that Select statement.
而事务中显式的那个可以(因为它有一个Begin事务语句)有其他语句(插入/更新/删除等等)在同一个事务中进行操作，可以是在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中，查询处理器总是处于三种事务模式之一:自动提交、隐式或显式。
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.
AutoCommit是SQL Server数据库引擎的默认事务管理模式。每个Transact-SQL语句在完成时都提交或回滚。如果语句成功完成，则提交;如果遇到任何错误，就回滚。这是默认的，在评论中是@Alex的问题的答案。
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.
在隐式交易模式下，“……”在提交或回滚当前事务之后，SQL Server数据库引擎将自动启动新的事务。你不做任何事情来描述交易的开始;您只提交或回滚每个事务。隐式交易模式产生连续的交易链。注意，斜体化的代码片段适用于每个事务，无论是单个事务还是多个语句事务。
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.
当您使用BEGIN事务语句显式启动事务时，引擎将处于显式事务模式。然后，在该事务中执行每个语句，直到显式终止事务(使用提交或回滚)，或者发生导致引擎终止和回滚的故障。
#2
6
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.
然后，我将在会话1中两次获得相同的信息(在删除它之前，表中的信息)。当我结束会话1的事务(提交、开始或回滚)并从会话中再次检查时，该表将显示为空。
#3
3
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语句将在读取提交级别与其他事务隔离。
Now what this means for your READ COMMITTED transaction isolation level: perhaps surprisingly, not that much.
现在，这对于您的读提交事务隔离级别意味着什么:也许令人惊讶的是，没有那么多。
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语句时，您将能够看到这些更改。在自动提交模式中，这两个select语句将在它们自己的事务中执行。如果另一个事务将修改并提交第一次选择的行，您将能够在第二次执行语句时看到这些更改。
#4
2
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).
读提交隔离级别是关于已编写的记录。它与这个select语句是否在事务中没有关系(除了在同一个事务中编写的那些东西)。
#1
55
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....
是的,里面的一个事务可以看到更改其他先前的插入/更新/删除语句的事务,一个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), - are in a transaction. The only difference between one that is explicitly in a transaction and one that is standing on it's own is that the one that is standing alone starts it's transaction immediately before it executes it, and commits or roll back immediately after it executes,
如果您要问的是隔离级别的作用，那么要理解——所有的Select语句(嘿，所有的语句)都在事务中。一个在事务中显式的唯一区别是独立存在的唯一的区别是在执行它之前独立启动它的事务，在它执行之后立即提交或回滚，
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) occcurring within that same transaction, either before or after that Select statement.
而事务中显式的那个可以(因为它有一个Begin事务语句)有其他语句(插入/更新/删除等等)在同一个事务中进行操作，可以是在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中，查询处理器总是处于三种事务模式之一:自动提交、隐式或显式。
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.
AutoCommit是SQL Server数据库引擎的默认事务管理模式。每个Transact-SQL语句在完成时都提交或回滚。如果语句成功完成，则提交;如果遇到任何错误，就回滚。这是默认的，在评论中是@Alex的问题的答案。
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.
在隐式交易模式下，“……”在提交或回滚当前事务之后，SQL Server数据库引擎将自动启动新的事务。你不做任何事情来描述交易的开始;您只提交或回滚每个事务。隐式交易模式产生连续的交易链。注意，斜体化的代码片段适用于每个事务，无论是单个事务还是多个语句事务。
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.
当您使用BEGIN事务语句显式启动事务时，引擎将处于显式事务模式。然后，在该事务中执行每个语句，直到显式终止事务(使用提交或回滚)，或者发生导致引擎终止和回滚的故障。
#2
6
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.
然后，我将在会话1中两次获得相同的信息(在删除它之前，表中的信息)。当我结束会话1的事务(提交、开始或回滚)并从会话中再次检查时，该表将显示为空。
#3
3
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语句将在读取提交级别与其他事务隔离。
Now what this means for your READ COMMITTED transaction isolation level: perhaps surprisingly, not that much.
现在，这对于您的读提交事务隔离级别意味着什么:也许令人惊讶的是，没有那么多。
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语句时，您将能够看到这些更改。在自动提交模式中，这两个select语句将在它们自己的事务中执行。如果另一个事务将修改并提交第一次选择的行，您将能够在第二次执行语句时看到这些更改。
#4
2
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).
读提交隔离级别是关于已编写的记录。它与这个select语句是否在事务中没有关系(除了在同一个事务中编写的那些东西)。