Does ResultSet.updateRow() commit, i.e. end the transaction?

According to the documentation ResultSet.updateRow() Updates the underlying database with the new contents of the current row of this ResultSet object.

Will this perform a commit, i.e. end the current transaction?

Answer

If auto-commit is disabled, then only explicitly calling Connection.commit() will commit a transaction. So, with auto-commit disabled, updating a result set row will not commit.

When auto-commit mode is enabled different rules apply. The JDBC 4.3 specification, section 10.1 Transaction Boundaries and Auto-commit says:

The Connection attribute auto-commit specifies when to end
transactions. Enabling auto-commit causes a transaction commit after
each individual SQL statement as soon as that statement is complete.
The point at which a statement is considered to be “complete” depends
on the type of SQL statement as well as what the application does
after executing it:

  • For Data Manipulation Language (DML) statements such as Insert, Update, Delete, and DDL statements, the statement is complete as soon
    as it has finished executing.
  • For Select statements, the statement is complete when the associated result set is closed.
  • For CallableStatement objects or for statements that return multiple results, the statement is complete when all of the associated
    result sets have been closed, and all update counts and output
    parameters have been retrieved.

As updating a row in a result set does not close the result set, it doesn’t complete the statement, and therefor it should not trigger a commit. So, based on the JDBC standard, updating a row in a result set in auto-commit mode should not commit until the result set is closed.

However, it is entirely possible that actual implementations do not adhere to this rule. For example, because the driver uses UPDATE statements to effect the change and the driver uses server-side auto-commit logic which will automatically commit once that UPDATE statement ends. Also, given the rules I quoted are a bit ambiguous, a driver could also interpret the rules that the update of a row is a DML update, and therefor should trigger a commit.

In other words, auto-commit mode and updatable result sets might behave differently between different JDBC drivers. I would recommend that when you want to use updatable result sets, that you disable auto-commit mode so your code is in control of the transaction boundary by calling Connection.commit() explicitly.

As an aside, using updatable result sets is almost always the wrong tool. Consider carefully if you really need to use them. They only really make sense in interactive user applications, and even then you should consider alternatives.

Leave a Reply

Your email address will not be published. Required fields are marked *