Should I commit or roll back a read transaction?

Question

I have a read request that I execute in a transaction to indicate the isolation level. Once the request is complete, what should I do?

• Commit transaction
• Transaction rollback
• Do nothing (which will lead to the rollback of the transaction at the end of the used block)

What are the implications of 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 whether to use a transaction or are there other ways to set the level of a transaction. The question is whether it makes any difference that a transaction that does not modify anything is committed or rollback. Is there a difference in performance? Does this affect other connections? Any other differences?

Answer 1

You commit. Period. There is no other reasonable alternative. If you started a transaction, you must close it. Committing releases any locks you may have had and is equally sensitive to ReadUncommitted or Serializable isolation levels. Relying on an implicit rollback - albeit technically equivalent - is simply a bad form.

If this did not convince you, just imagine the next guy who inserts the update statement in the middle of your code and needs to track the implicit rollback that happens and delete his data.

Answer 2

If you have not changed anything, you can use either COMMIT or ROLLBACK. Any of them will release any read locks that you acquired, and since you have not made any other changes, they will be equivalent.

Answer 3

If you are starting a transaction, then the best practice is always to commit it. If an exception is thrown in the use (transaction) block, the transaction will be automatically canceled.

Answer 4

IMHO it might make sense to wrap only read requests in transactions (especially in Java), you can say that the transaction is “read-only”, which, in turn, can use the JDBC driver to optimize the request (but it doesn’t matter, so no one will stop you from issuing an INSERT nonetheless). For example. the Oracle driver will completely avoid table locks on queries in a read-only transaction that gets better performance when working with highly readable applications.

Answer 5

Just a note, but you can also write this code as follows:

 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 restructure things a bit, perhaps you can also move the usage block for IDataReader up.

Answer 6

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

 set transaction isolation level read uncommitted 

then you don't need to jump over any hoops of C # code. Setting the transaction isolation level in the stored procedure does not make this parameter applicable to all future uses of this connection (that you need to worry about other settings, since the connections are merged). At the end of the stored procedure, it simply returns to what was initialized by the connection.

Answer 7

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

We need to close transactions with COMMIT (for success) and ROLLBACK (for failure), even in the case of read-only transactions, where this does not seem to matter. In fact, it matters for consistency and future verification.

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

• the query does not return exactly one row as expected
• stored procedure throws an exception
• found that data selection is incompatible.
• user aborts transaction because it is too long
• deadlock or timeout

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

Implicit ROLLBACK should be used only for situations with a “fatal error”, when the application crashes or fails with a fatal error, network failure, power failure, etc.

Answer 8

Consider nested transactions .

Most RDBMS do not support nested transactions or try to mimic them in a very limited way.

For example, in MS SQL Server, rollback in an internal transaction (which is not a real transaction, MS SQL Server simply counts transaction levels!) Will roll back everything that happened in an outmost transaction (which is a real transaction).

Some database wrappers may consider rollback in an internal transaction as an indication that an error has occurred, and rollback of everything in the external transaction itself, regardless of whether the transaction was completed or the most recent transaction was completed.

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

Please note that this is a general answer to the question. The sample code skilfully works around a problem with an external transaction, opening up a new connection to the database.

Regarding performance: depending on isolation level, SELECTs may require varying degrees of LOCK and temporal data (snapshots). This is cleared when the transaction is closed. It doesn’t matter if this is done through COMMIT or ROLLBACK. There may be a slight difference in the processor time spent - COMMIT is probably faster to understand than ROLLBACK (two less characters) and other minor differences. Obviously, this is true only for read-only operations!

Not completely requested: another programmer who can read the code may suggest that ROLLBACK implies an error condition.

Answer 9

Given that READ does not change state, I did nothing. A commit will do nothing except waste that sends a request to the database. You did not perform an operation that changed state. Similarly for rollback.

However, you must clear your objects and close your database connections. Not closing your connections can cause problems if this code is called repeatedly.

Answer 10

If you set AutoCommit to false, then YES.

In exp with JDBC (Postgresql driver), I found that if you select query breaks (due to a timeout), you will not be able to initiate a new query if you don’t refuse.

Answer 11

In your sample code, where do you have

• // Do something useful

  Is an SQL statement executing that modifies the data?

If not, then there is no such thing as a Read transaction ... Only changes from the insert, update, and delete statements (statements that can modify data) are in the transaction ... What you say is that it blocks what SQL Server puts The data you are reading is due to OTHER transactions that affect this data. The level of these locks depends on the isolation level of SQL Server.

But you cannot Commit or ROll Back anything unless your SQL statement has changed anything.

If you change the data, you can change the isolation level without explicitly starting the transaction ... Each individual SQL expression is implicitly in the transaction. The explicit start of a transaction is only necessary to ensure that two or more operators are in the same transaction.

If all you want to do is set the transaction isolation level, then just set the CommandText command to "Set the transaction isolation level to Repeat Read" (or whatever level you want), set CommandType to CommandType.Text and run the command. ( you can use Command.ExecuteNonQuery ())

NOTE. If you are executing MULTIPLE read instructions and want all of them to “display” the same database state as the first, then you need to set the isolation level to Top repeat. Reading or Serializable ...

Answer 12

Do you need to block others from reading the same data? Why use a transaction?

@Joel - my question will be better worded as "Why use a transaction to read?"

@Stefan. If you intend to use AdHoc SQL rather than a stored procedure, just add WITH (NOLOCK) after the tables in the query. Thus, you do not get the overhead (albeit minimally) in the application and database for the transaction.

 SELECT * FROM SomeTable WITH (NOLOCK) 

EDIT @Comment 3: Since you had "sqlserver" in the question tags, I assumed that MSSQLServer was the target product. Now that this point has been clarified, I edited the tags to remove the specific product link.

I'm still not sure why you want to complete a transaction on a read-first basis.