CODEWITHSUNDEEP
javajdbctransactionscommit
Roland
Roland

Reputation: 7853

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?

Upvotes: 0

Views: 855

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109016

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.

Upvotes: 4

Related Questions