SQL 我应该提交还是回滚读取事务？

Should I commit or rollback a read transaction?

提问by Stefan Moser

I have a read query that I execute within a transaction so that I can specify the isolation level. Once the query is complete, what should I do?

我有一个在事务中执行的读取查询，以便我可以指定隔离级别。查询完成后，我该怎么办？

• Commit the transaction
• Rollback the transaction
• Do nothing (which will cause the transaction to be rolled back at the end of the using block)

• 提交交易
• 回滚事务
• 什么都不做（这会导致事务在 using 块结束时回滚）

What are the implications of doing each?

做每件事有什么影响？

using (IDbConnection connection = ConnectionFactory.CreateConnection())
{
    using (IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        using (IDbCommand command = connection.CreateCommand())
        {
            command.Transaction = transaction;
            command.CommandText = "SELECT * FROM SomeTable";
            using (IDataReader reader = command.ExecuteReader())
            {
                // Read the results
            }
        }

        // To commit, or not to commit?
    }
}

EDIT: The question is not if a transaction should be used or if there are other ways to set the transaction level. The question is if it makes any difference that a transaction that does not modify anything is committed or rolled back. Is there a performance difference? Does it affect other connections? Any other differences?

编辑：问题不在于是否应该使用事务或是否有其他方法来设置事务级别。问题是提交或回滚不修改任何内容的事务是否有任何区别。有性能差异吗？它会影响其他连接吗？还有其他区别吗？

采纳答案by Mark Brackett

You commit. Period. There's no other sensible alternative. If you started a transaction, you should close it. Committing releases any locks you may have had, and is equally sensible with ReadUncommitted or Serializable isolation levels. Relying on implicit rollback - while perhaps technically equivalent - is just poor form.

你承诺。时期。没有其他明智的选择。如果您开始了一个事务，您应该关闭它。提交会释放您可能拥有的任何锁，并且对于 ReadUncommitted 或 Serializable 隔离级别同样明智。依赖隐式回滚——虽然在技术上可能是等价的——只是糟糕的形式。

If that hasn't convinced you, just imagine the next guy who inserts an update statement in the middle of your code, and has to track down the implicit rollback that occurs and removes his data.

如果这还没有说服您，请想象下一个在您的代码中间插入更新语句的人，并且必须追踪发生的隐式回滚并删除他的数据。

回答by Graeme Perrow

If you haven't changed anything, then you can use either a COMMIT or a ROLLBACK. Either one will release any read locks you have acquired and since you haven't made any other changes, they will be equivalent.

如果您没有更改任何内容，那么您可以使用 COMMIT 或 ROLLBACK。任何一个都将释放您获得的任何读锁，并且由于您没有进行任何其他更改，因此它们将是等效的。

回答by Neil Barnwell

If you begin a transaction, then best practice is always to commit it. If an exception is thrown inside your use(transaction) block the transaction will be automatically rolled-back.

如果您开始一个事务，那么最佳实践总是提交它。如果在您的 use(transaction) 块中抛出异常，则事务将自动回滚。

回答by Oliver Drotbohm

IMHO it can make sense to wrap read only queries in transactions as (especially in Java) you can tell the transaction to be "read-only" which in turn the JDBC driver can consider optimizing the query (but does not have to, so nobody will prevent you from issuing an INSERTnevertheless). E.g. the Oracle driver will completely avoid table locks on queries in a transaction marked read-only, which gains a lot of performance on heavily read-driven applications.

恕我直言，将只读查询包装在事务中是有意义的，因为（尤其是在 Java 中）您可以告诉事务为“只读”，这反过来 JDBC 驱动程序可以考虑优化查询（但不必这样做，所以没有人将阻止您发出INSERT尽管如此）。例如，Oracle 驱动程序将完全避免在标记为只读的事务中的查询上的表锁定，这在大量读取驱动的应用程序中获得了很多性能。

回答by Klaws

Consider nested transactions.

考虑嵌套事务。

Most RDBMSes do not support nested transactions, or try to emulate them in a very limited way.

大多数 RDBMS 不支持嵌套事务，或者尝试以非常有限的方式模拟它们。

For example, in MS SQL Server, a rollback in an inner transaction (which is not a real transaction, MS SQL Server just counts transaction levels!) will rollback the everything which has happened in the outmosttransaction (which is the real transaction).

例如，在 MS SQL Server 中，内部事务（不是真正的事务，MS SQL Server 只计算事务级别！）中的回滚将回滚最外层事务（即真正的事务）中发生的一切。

Some database wrappers might consider a rollback in an inner transaction as an sign that an error has occured and rollback everything in the outmost transaction, regardless whether the outmost transaction commited or rolled back.

一些数据库包装器可能将内部事务中的回滚视为发生错误的标志，并回滚最外层事务中的所有内容，无论最外层事务是提交还是回滚。

So a COMMIT is the safe way, when you cannot rule out that your component is used by some software module.

因此，当您不能排除某些软件模块使用您的组件时，COMMIT 是一种安全的方式。

Please note that this is a general answer to the question. The code example cleverly works around the issue with an outer transaction by opening a new database connection.

请注意，这是对该问题的一般性回答。该代码示例通过打开一个新的数据库连接巧妙地解决了外部事务的问题。

Regarding performance: depending on the isolation level, SELECTs may require a varying degree of LOCKs and temporary data (snapshots). This is cleaned up when the transaction is closed. It does not matter whether this is done via COMMIT or ROLLBACK. There might be a insignificant difference in CPU time spent - a COMMIT is probably faster to parse than a ROLLBACK (two characters less) and other minor differences. Obviously, this is only true for read-only operations!

关于性能：根据隔离级别，SELECT 可能需要不同程度的 LOCK 和临时数据（快照）。这在交易关闭时被清除。这是通过 COMMIT 还是 ROLLBACK 完成的并不重要。花费的 CPU 时间可能存在微不足道的差异 - COMMIT 的解析速度可能比 ROLLBACK（少两个字符）和其他细微差异更快。显然，这仅适用于只读操作！

Totally not asked for: another programmer who might get to read the code might assume that a ROLLBACK implies an error condition.

完全没有要求：另一个可能会阅读代码的程序员可能会认为 ROLLBACK 意味着错误条件。

回答by Joel Coehoorn

Just a side note, but you can also write that code like this:

只是一个旁注，但您也可以像这样编写代码：

using (IDbConnection connection = ConnectionFactory.CreateConnection())
using (IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted))
using (IDbCommand command = connection.CreateCommand())
{
    command.Transaction = transaction;
    command.CommandText = "SELECT * FROM SomeTable";
    using (IDataReader reader = command.ExecuteReader())
    {
        // Do something useful
    }
    // To commit, or not to commit?
}

And if you re-structure things just a little bit you might be able to move the using block for the IDataReader up to the top as well.

如果您稍微重新构建一些东西，您也可以将 IDataReader 的 using 块移到顶部。

回答by Sam Watkins

ROLLBACK is mostly used in case of an error or exceptional circumstances, and COMMIT in the case of successful completion.

ROLLBACK 多用于出错或异常情况，COMMIT 用于成功完成。

We should close transactions with COMMIT (for success) and ROLLBACK (for failure), even in the case of read-only transactions where it doesn't seem to matter. In fact it does matter, for consistency and future-proofing.

我们应该用 COMMIT（为了成功）和 ROLLBACK（为了失败）关闭事务，即使是在只读事务的情况下似乎无关紧要。事实上，为了一致性和面向未来，它确实很重要。

A read-only transaction can logically "fail" in many ways, for example:

只读事务可以在许多方面在逻辑上“失败”，例如：

• a query does not return exactly one row as expected
• a stored procedure raises an exception
• data fetched is found to be inconsistent
• user aborts the transaction because it's taking too long
• deadlock or timeout

• 查询未按预期准确返回一行
• 存储过程引发异常
• 发现获取的数据不一致
• 用户中止交易，因为它花费的时间太长
• 死锁或超时

If COMMIT and ROLLBACK are used properly for a read-only transaction, it will continue to work as expected if DB write code is added at some point, e.g. for caching, auditing or statistics.

如果 COMMIT 和 ROLLBACK 正确用于只读事务，如果在某个点添加数据库写入代码，例如用于缓存、审计或统计，它将继续按预期工作。

Implicit ROLLBACK should only be used for "fatal error" situations, when the application crashes or exits with an unrecoverable error, network failure, power failure, etc.

隐式 ROLLBACK 仅应用于“致命错误”情况，即应用程序崩溃或退出时出现不可恢复的错误、网络故障、电源故障等。

回答by Eric Z Beard

If you put the SQL into a stored procedure and add this above the query:

如果将 SQL 放入存储过程并在查询上方添加以下内容：

set transaction isolation level read uncommitted

then you don't have to jump through any hoops in the C# code. Setting the transaction isolation level in a stored procedure does not cause the setting to apply to all future uses of that connection (which is something you have to worry about with other settings since the connections are pooled). At the end of the stored procedure it just goes back to whatever the connection was initialized with.

那么您就不必跳过 C# 代码中的任何环节。在存储过程中设置事务隔离级别不会导致该设置应用于该连接的所有未来使用（这是您必须担心的其他设置，因为连接是池化的）。在存储过程结束时，它只是返回到初始化连接的任何内容。

回答by Brett McCann

Given that a READ does not change state, I would do nothing. Performing a commit will do nothing, except waste a cycle to send the request to the database. You haven't performed an operation that has changed state. Likewise for the rollback.

鉴于 READ 不会改变状态，我什么都不做。执行提交不会做任何事情，除了浪费一个周期将请求发送到数据库。您尚未执行更改状态的操作。回滚也是如此。

You should however, be sure to clean up your objects and close your connections to the database. Not closing your connections can lead to issues if this code gets called repeatedly.

但是，您应该确保清理对象并关闭与数据库的连接。如果此代码被重复调用，不关闭连接可能会导致问题。

回答by Shiv Krishna Jaiswal

If you set AutoCommit false, then YES.

如果您将 AutoCommit 设置为 false，则为 YES。

In an experiment with JDBC(Postgresql driver), I found that if select query breaks(because of timeout), then you can not initiate new select query unless you rollback.

在使用JDBC（Postgresql驱动程序）进行实验时，我发现如果选择查询中断（由于超时），则除非回滚，否则无法启动新的选择查询。