Issue Details (XML | Word | Printable)

Key: JDBC-399
Type: New Feature New Feature
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Mark Rotteveel
Reporter: Mark Rotteveel
Votes: 0
Watchers: 0

If you were logged in you would be able to see more operations.
Jaybird JDBC Driver

Use isc_tpb_autocommit for autocommit, instead of starting and committing transactions for each action.

Created: 30/May/15 08:58 AM   Updated: 19/Oct/15 03:40 PM
Component/s: JDBC driver
Affects Version/s: None
Fix Version/s: Jaybird 2.2.9, Jaybird 3.0.0

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 19/Jul/15 04:26 PM
Implementation added to 2.2.9 and 3.0. This option is enabled by specifying the connection property useFirebirdAutocommit=true

With this option, Jaybird will configure the transaction to use isc_tpb_autocommit with autoCommit=true and not commit itself until connection close (or switching to autoCommit=false). The exception is if the statement was of type isc_info_sql_stmt_ddl, in that case Jaybird will commit on statement success and rollback on statement failure (just like it does for all statements in normal auto commit mode). The reason is that Firebird for some DDL commands only executes at a real commit boundary and relying on the Firebird autocommit is insufficient.

On statement completion (as specified in JDBC), result sets will still close unless they are holdable over commit. The result set is only closed clientside, which means that the cursor remains open server side to prevent roundtrips. This may lead to additional resource usage server side unless explicitly closed in the code. Note that any open blobs will be closed client and serverside (until this is improved with JDBC-401).

A connection can be interrogated using FirebirdConnection.isUseFirebirdAutocommit() if it uses isc_tpb_autocommit.

If you manually add isc_tpb_autocommit to the transaction parameter buffer and you enable this option, the isc_tpb_autocommit will be removed from the TPB if autoCommit=false.

Support for this option is experimental, and should only be enabled if you 1) know what you're doing, and 2) if you really need this feature. Internally isc_tpb_autocommit uses commit_retaining, which means that using this feature may increase the transaction gap with associated sweep and garbage collection impact.

Whether or not this improves performance has yet to be tested. The Jaybird testsuite does not show a noticeable difference either way, but most tests only use a a few statements on a single connection.

Mark Rotteveel added a comment - 24/Jul/15 11:41 AM

Mark Rotteveel added a comment - 19/Oct/15 11:10 AM
Artificial testing with repeated inserts (using a prepared statement) against a Firebird server on localhost shows that this leads to a reduction of execution time of +/- 7%.