Should a read transaction be committed or rolled back?

Problem

I have a read query that I run within a transaction to specify the amount of isolation. What should I do once the query is finished?

What are the ramifications of each action?

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 isn’t whether or not a transaction should be utilized, but rather whether or not there are other options for setting the transaction level. The question is whether a transaction that does not affect anything should be committed or rolled back. Is there a difference in performance? Does it have an impact on other connections? Are there any additional differences?

Asked by Stefan Moser

Solution #1

You make a commitment. Period. There is no other reasonable option. If you’ve started a transaction, it’s time to finish it. Committing releases whatever locks you may have held, and it works with ReadUncommitted or Serializable isolation levels equally well. It’s bad form to rely on implicit rollback, even if it’s theoretically equal.

If that isn’t enough to persuade you, consider the next person who puts an update statement in the midst of your code and then has to track for the implicit rollback that destroys his data.

Answered by Mark Brackett

Solution #2

You can use either a COMMIT or a ROLLBACK if you haven’t modified anything. Any read locks you’ve obtained will be released by either option, and since you haven’t made any other modifications, they’ll be comparable.

Answered by Graeme Perrow

Solution #3

When you start a transaction, the ideal practice is to finish it. The transaction will be rolled back if an exception is raised inside your use(transaction) block.

Answered by Neil Barnwell

Solution #4

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 INSERT nevertheless). For example, the Oracle driver will totally avoid table locks on queries in a read-only transaction, which improves efficiency significantly in strongly read-driven applications.

Answered by Oliver Drotbohm

Solution #5

Consider nested transactions.

Nested transactions aren’t supported by most RDBMSs, or they try to replicate them in a restricted way.

In MS SQL Server, for example, a rollback in an inner transaction (which isn’t actually a transaction; MS SQL Server only counts transaction levels!) will undo everything that happened in the outmost transaction (which is the real transaction).

Some database wrappers may interpret a rollback in an inner transaction as an indication of a mistake and rollback everything in the outmost transaction, regardless of whether it committed or rolled back.

When you can’t rule out the possibility that your component is used by another software module, a COMMIT is the safest option.

Please keep in mind that this is a broad response to the topic. By initiating a new database connection, the code example cleverly avoids the problem of an outer transaction.

In terms of performance, SELECTs may necessitate variable degrees of LOCKs and temporary data, depending on the isolation level (snapshots). When the transaction is completed, this gets cleaned up. It makes no difference whether you use COMMIT or ROLLBACK to accomplish this. There could be a tiny variation in CPU time consumed – for example, a COMMIT is probably faster to parse than a ROLLBACK (two characters less) and other minor changes. This obviously applies solely to read-only activities!

Another programmer who would view the code might conclude that a ROLLBACK indicates an erroneous state, which is completely unintentional.

Answered by Klaws

Post is based on https://stackoverflow.com/questions/309834/should-i-commit-or-rollback-a-read-transaction