Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Use isc_tpb_autocommit for autocommit, instead of starting and committing transactions for each action. [JDBC399] #441

Closed
firebird-automations opened this issue May 30, 2015 · 6 comments

Comments

@firebird-automations
Copy link

Submitted by: @mrotteveel

See #1

Commits: a3fd460 433b748 a1174c5 e5dea94 53e09c8 309db7f 4d720d6 d1c208f

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Fix Version: Jaybird 2.2.9 [ 10691 ]

Fix Version: Jaybird 3.0 [ 10440 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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 JDBC401).

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.

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

A test version is available from https://github.com/FirebirdSQL/jaybird/releases/tag/v2.2.9-SNAPSHOT

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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%.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants