Do I Need to Call Rollback If I Never Commit

Do I need to call rollback if I never commit?

It should roll back on close of connection. Emphasis on should for a reason :-)

Proper transaction and error handling should have you always commit when the conditions for commit are met and rollback when they aren't. I think it is a great habit to always commit or rollback when done and not rely on disconnect/etc. All it takes is one mistake or incorrectly/not closed session to create a blocking chain nightmare for all :-)

Do I need to manually roll back a transaction if not committing changes given some conditions?

You don't need to call Rollback manually because you are using the using statement.
It is not necessary to explicitly call Rollback. The transaction will be rolled-back if Commit() has not been called.

DbContextTransaction.Dispose method will be called in the end of the using block. And it will automatically rollback the transaction if the transaction is not successfully committed

Java SQLite: Is it necessary to call rollback when auto commit is false and a transaction fails?

Quick answer: The fact that you're asking means you're doing it wrong, probably. However, if you must know: Yes, you need to explicitly rollback.

What is happening under the hood

At the JDBC level (and if you're using JOOQ, JDBI, Hibernate, or something similar, that's a library built on top of JDBC usually), you have a Connection instance. You'd have gotten this via DriverManager.getConnection(...) - or a connection pooler got it for you, but something did.

That connection can be in the middle of a transaction (auto-commit mode merely means that the connection assumes you meant to write an additional commit() after every SQL statement you care to run in that connection's context, that's all auto-commit does, but, obviously, if that's on, you probably are in a 'clean' state, that is, the last command processed by that connection was either COMMIT or ROLLBACK).

If it is in the middle of a transaction and you close the connection, the ROLLBACK is implicit.

The connection has to make a choice, it can't keep existing, so, it commits or rolls back. The spec guarantees it doesn't just commit for funsies on you, so, therefore, it rolls back.

The question then boils down to your specific setup. This, specifically, is dangerous:

try (Connection con = ...) {
  con.setAutoCommit(false);
  try {
    try (var s = con.createStatement()) {
      s.execute("DROP TABLE foobar");
    }
  } catch (SQLException ignore) {
    // ignoring an exception usually bad idea. But for sake of example..
  }

  // A second statement on the same connection...
  try (var s = con.createStatement()) {
    s.execute("DROP TABLE quux");
  }
}

A JDBC driver is, as far as the spec is concerned, free to throw an SQLException along the lines of 'the connection is aborted; you must explicitly rollback first then you can use it again' on the second statement.

However, the above code is quite bad. You cannot use transaction isolation level SERIALIZABLE at all with this kind of code (once you get more than a handful of users, the app will crash and burn in a cavalcade of retry exceptions), and it is either doing something useless (re-using 1 connection for multiple transactions when you have a connection pooler in use), or is solving a problem badly (the problem of: Using a new connection for every transaction is pricey).

1 transaction, 1 connection

The only reason the above was dangerous is because we're doing two unrelated things (namely: 2 transactions) in a single try-block associated with a connection object. We're re-using the connection. This is a bad idea: connections have baggage associated with them: Properties that were set, and, yes, being in 'abort' state (where an explicit ROLLBACK is required before the connection is willing to execute any other SQL). By just closing the connection and getting a new one, you ditch all that baggage. This is the kind of baggage that results in bugs that unit tests are not going to catch easily, a.k.a. bugs that, if they ever trigger, cost a ton of money / eyeballs / goodwill / time to fix. Objectively you must prefer 99 easy-to-catch bugs if it avoids a single 100x-harder-to-catch bug, and this is one of those bugs that falls in the latter category.

Connections are pricey? What?

There's one problem with that: Just use a connection for a single transaction and then hand it back, which thus eliminates the need to rollback, as the connection will do that automatically if you close() it: Getting connections is quite resource-heavy.

So, folks tend to / should probably be using a connection pooler to avoid this cost. Don't write your own here either; use HikariCP or something like it. These tools pool connections for you: Instead of invoking DriverManager.getConnection, you ask HikariCP for one, and you hand your connection back to HikariCP when you're done with it. Hikari will take care of resetting it for you, which includes rolling back if the connection is halfway inside a transaction, and tackling any other per-connection settings, getting it back to known state.

The common DB interaction model is essentially this 'flow':

someDbAccessorObject.act(db -> {
  // do a single transaction here
});

and that's it. This code, under the hood, does all sorts of things:

• Uses a connection pooler.
• Sets up the connection in the right fashion, which primarily involves setting auto-commit to false, and setting the right transaction isolation level.
• will COMMIT at the end of the lambda block, if no exceptions occurred. Hands back the connection in either case, back to the pool.
• Will catch SQLExceptions and analyse if they are retry exceptions. If yes, does nagle's algorithm or some other randomized exponential backoff and reruns the lambda block (that's what retry exceptions mean).
• Takes care of having the code that 'gets' a connection (e.g. determines the right JDBC url to use) in a single place, so that a change in db config does not entail going on a global search/replace spree in your codebase.

In that model, it is somewhat rare that you run into your problem, because you end up in a '1 transaction? 1 connection!' model. Ordinarily that's pricey (creating connections is far more expensive that rolling back/committing as usual and then just continuing with a new transaction on the same connection object), but it boils down to the same thing once a pooler is being used.

In other words: Properly written DB code should not have your problem unless you're writing a connection pooler yourself, in which case the answer is definitely: roll back explicitly.

Does ROLLBACK TRANSACTION imply a commit if not rolled-back

No. The transaction will remain open and uncommitted until it is committed, which will probably eventually cause blocking on your database.

If transactions are nested, rollback will rollback to the beginning of the outer transaction, not the inner transaction - See http://msdn.microsoft.com/en-us/library/ms181299.aspx

I would consider a structure something like

 begin try
      begin tran
      -- do query
      commit tran
 end try
 begin catch
      if @@trancount>0 
      begin
          rollback
      end
      -- handle error here
 end catch

What happens if you don't commit a transaction to a database (say, SQL Server)?

As long as you don't COMMIT or ROLLBACK a transaction, it's still "running" and potentially holding locks.

If your client (application or user) closes the connection to the database before committing, any still running transactions will be rolled back and terminated.

Is it necessary to write ROLLBACK if queries fail?

I think you're asking if executing ROLLBACK is necessary, since without it the commits still don't get applied. That's technically true, but only because the transaction is still open since you haven't ended it. Anything that implicitly commits the transaction (for example, starting a new transaction) will act as though you ran COMMIT, which is the opposite of what you want

Why do we need a Rollback command while making a transaction or tell me a when is it appropriate to use ROLLBACK

Using the rollback method, you can close the transaction, reverting all associated changes made to the database.
Eg:

Above diagram illustrate the three query at a time.Tansaction of query1 & query2 were successful but query3 got error. And when query3 got error all transaction will revert.

What happens if transaction.Rollback/Commit never called in before closing the connection?

What happens if transaction.Rollback/Commit never called in before closing the connection ?

In MySQL the transaction is rolled back. But some other table servers commit it on connection close.

Pro tip: Don't rely on this behavior except as a way to handle a hard crash.

Is it a better practice to explicitly call transaction rollback or let an exception trigger an implicit rollback?

Former. If you look up MSDN samples on similar topics, like TransactionScope, they all favor the implicit rollback. There are various reasons for that, but I'll just give you a very simple one: by the time you catch the exception, the transaction may had already rolled back. Many errors rollback the pending transaction and then they return control to the client, where the ADO.Net raises the CLR SqlException after the transaction was already rolled back on the server (1205 DEADLOCK is the typical example of such an error), so the explicit Rollback() call is, at best, a no-op, and at worse an error. The provider of the DbTransaction (eg. SqlTransaction) should know how to handle this case, eg. because there is explicit chat between the server and the client notifying of the fact that the transaction rolled back already, and the Dispose() method does the right thing.

A second reason is that transactions can be nested, but the semantics of ROLLBACK are that one rollback rolls back all transactions, so you only need to call it once (unlike Commit() which commits only the inner most transaction and has to be called paired up for each begin). Again, Dispose() does the right thing.

Update

The MSDN sample for SqlConnection.BeginTransaction() actually favors the second form and does an explicit Rollback() in the catch block. I suspect the technical writer simply intended to show in one single sample both Rollback() and Commit(), notice how he needed to add a second try/catch block around the Rollback to circumvent exactly some of the problems I mentioned originally.

---

Related Topics