With Oracle JDBC, what problems will I face if I do not COMMIT select-only transactions

user_80779 0
Viewed 288

In order to connect to my Oracle DB, I use the thin Oracle JDBC driver and the tomcat JDBC connection pool.

The default setting tomcat-pool is not to touch the connection when it is returned to the pool (any associated transaction is not committed or rolled back, and the actual connection is not closed).

The default transaction isolation level of the Oracle driver is READ_COMMITTED, and auto-commit is set to false

I have several business methods that use the same connection pool to connect to the database. In some business methods, I explicitly create a new transaction, issue some updates and commit that transaction. So far so good.

Some other business methods are read-only. They are read-only because I only issue SELECT queries, not because I've explicitly set READ ONLY mode on the connection (it stays off). My understanding is that under these conditions, Oracle creates a new implicit transaction the first time is encounters a SELECT statement, and keeps that transaction open until I explicitly COMMIT it.

However, given that I only issue SELECT queries, I don't feel that it is necessary to commit the transaction.

I understand that not committing those read-only transactions before returning their connections to the pool means that then next time the connection is borrowed from the pool, the next business method will execute within a transaction that was already started earlier.

My question are:

  • Does it matter?
  • Given the transaction isolation is set to READ_COMMITTED and auto-commit is false, is there any potential extra data race condition that wouldn't have existed if I committed my read-only transaction?
  • Does not committing the transaction incur performance costs on the DB server side (the transaction could potentially stay uncommitted for hours)?
  • Do the answers to the above questions change if we are connecting to RAC instead of a single Oracle DB instance?
  • What about if I'm selecting from a DB Link?

From all the resources I've read, my conclusion so far is that what I'm doing is technically not correct, but that as long as I don't pick a higher transaction isolation level it also doesn't matter. READ_COMMITTED will read whatever was most recently committed by other transactions regardless of whether or not the current transaction is committed or not, and the overhead on the oracle server for keeping track of a transaction that has no pending modifications is trivial. So fixing this issue falls into the "should be fixed, but not an emergency" category.

Does that make sense or did I misunderstand something important?

jdbc transactions oracle java
1 Answers

Like Alex Poole commented, it's true that the database engine does not necessarily need to create a transaction for certain cases of read operations for correct operation. I'm not familiar with Oracle internals, but I'll trust Alex that running this query after a select query will show that no actual TX was created.

However, the database engine is the lowest level and knows everything. On top of that we have DB client programs, one of which is the JDBC driver. The JDBC has its own standard, which includes things like setAutocommit(), a thing that databases don't know about, and which is used for transaction handling in JDBC code. Note that setAutocommit(false) is an abstraction, and it does not mean "start transaction", it just means that operations will be grouped in a transaction in some way.

This is where the JDBC vs. DB engine differences start. I'm basing this on PostgreSQL driver code (as it is more readily available), but I expect the Oracle driver to behave in similar way.

Since the driver cannot easily know whether a TX is needed or not, it cannot do heavy optimization like the actual DB engine. The PostgreSQL driver has a SUPPRESS_BEGIN flag that can be used to indicate that a transaction isn't needed even if one would be started otherwise, but based on a quick look it's used only when fetching metadata, an operation known to be safe from transaction issues. Otherwise the driver will issue a BEGIN to start a transaction even for a SELECT, when autocommit=false.

So even if the DB engine doesn't need a TX, the driver is an abstraction on top of the DB, working with JDBC specs. Since you've observed open transactions, it's safe to assume that the Oracle driver also creates explicit transactions for operations where the Oracle DB engine would not create implicit transactions.

So, does it matter?

As discussed in the comments, probably won't make a difference when you have some simple selects to local tables as the transactions aren't holding up significant resources. But throw in DB Links, changes to queries over time, and there's a non-zero amount of risk.

user_25415600
Related