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

When working with a SQLite database in Java, suppose I set auto commit to false. Is it necessary when a SQLException occurs that I call rollback() method? Or can I simply ignore calling it and the transaction will automatically be rolled back (all the changes I made during the transaction will be undone automatically)?

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.