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

Setting transaction isolation resets previously set parameters [JDBC382] #424

Closed
firebird-automations opened this issue Feb 26, 2015 · 5 comments

Comments

@firebird-automations
Copy link

Submitted by: Attila Molnár (e_pluribus_unum)

Hi Mark!

Calling Conection.setTransactionIsolation() resets to (url) default transaction parameters! previously setted flags are lost!
Is this intended (defined by JDBC standard) or this is a bug?

The problem is in then org.firebirdsql.jca.FBManagedConnection class.

public void setTransactionIsolation\(int isolation\) throws ResourceException \{
    transactionIsolation = isolation;
    
    tpb = mcf\.getTpb\(isolation\);//this resets to \(url\) default
\}

Test case :

BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("org.firebirdsql.jdbc.FBDriver");
ds.setUrl("jdbc:firebirdsql:l3s-4/3051:d:\\databases\\report.fdb?charSet=UTF-8&isc_dpb_sql_dialect=1");
ds.setUsername("SYSDBA");
ds.setPassword("masterkey");
BasicDataSource ds2 = new BasicDataSource();
ds2.setDriverClassName("org.firebirdsql.jdbc.FBDriver");
ds2.setUrl("jdbc:firebirdsql:l3s-4/3051:d:\\databases\\report.fdb?charSet=UTF-8&isc_dpb_sql_dialect=1");
ds2.setUsername("SYSDBA");
ds2.setPassword("masterkey");
FirebirdConnection fc = (FirebirdConnection) ds.getConnection().unwrap(FirebirdConnection.class);
TransactionParameterBuffer tpb = fc.createTransactionParameterBuffer();
tpb.addArgument(TransactionParameterBuffer.WRITE);
tpb.addArgument(TransactionParameterBuffer.READ_COMMITTED);
tpb.addArgument(TransactionParameterBuffer.REC_VERSION);
tpb.addArgument(TransactionParameterBuffer.NOWAIT);
fc.setTransactionParameters(tpb);
fc.setAutoCommit(false);
FirebirdConnection fc2 = (FirebirdConnection) ds2.getConnection().unwrap(FirebirdConnection.class);
TransactionParameterBuffer tpb2 = fc2.createTransactionParameterBuffer();
tpb2.addArgument(TransactionParameterBuffer.WRITE);
tpb2.addArgument(TransactionParameterBuffer.READ_COMMITTED);
tpb2.addArgument(TransactionParameterBuffer.REC_VERSION);
tpb2.addArgument(TransactionParameterBuffer.NOWAIT);
fc2.setTransactionParameters(tpb2);
fc2.setAutoCommit(false);

ResultSet rs = fc.createStatement().executeQuery("SELECT * FROM rdb$database WITH LOCK");
rs.next();

try {
ResultSet rs2 = fc2.createStatement().executeQuery("SELECT * FROM rdb$database WITH LOCK");
rs2.next();
}
catch (SQLException e) {
System.out.println(e);//lock conflict, because of nowait flag
}
rs.close();
fc.commit();
fc2.commit();
fc2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);//this resets to (url) default transaction parameters! nowait flag lost!
rs = fc.createStatement().executeQuery("SELECT * FROM rdb$database WITH LOCK");
rs.next();
try {
ResultSet rs2 = fc2.createStatement().executeQuery("SELECT * FROM rdb$database WITH LOCK");
rs2.next();//indefinitely wait :(
}
catch (SQLException e) {
System.out.println(e);
}

Thank you!

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

The FirebirdConnection.setTransactionParameters(TransactionParameterBuffer) method is a non-standard method specific to Firebird, so the JDBC specification has little to say about this. The behavior you see is expected and intended, as documented at http://www.firebirdsql.org/file/documentation/drivers_documentation/java/2.2.7/docs/org/firebirdsql/jdbc/FirebirdConnection.html#setTransactionParameters-org.firebirdsql.gds.TransactionParameterBuffer- :

"Set transaction parameters for the next transactions. This method does not change the TPB mapping, but replaces the mapping for the current transaction isolation **until Connection.setTransactionIsolation(int) is called.**"

You either need to use FirebirdConnection.setTransactionParameters(int, TransactionParameterBuffer) to permanently change the mapping of one of the isolation levels, or you can set this in the URL by adding a property to the URL with the isolation level name and the transaction buffer options, eg TRANSACTION_READ_COMMITTED= isc_tpb_read_committed,isc_rec_version,isc_tpb_write,isc_tpb_nowait

(see also the Jaybird 2.1 programmers manual, page 64)

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Open [ 1 ] => Closed [ 6 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Author

Commented by: Attila Molnár (e_pluribus_unum)

I've got problem with the JDBC level Conection.setTransactionIsolation() method, not Jaybird's setTransactionParameters() method.

And my poblem is that JDBC setTransactionParameters() call changes not just isolation parameter of the transaction.

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

That is intentional. The effect of setTransactionParameters(TransactionParameterBuffer) lasts only until setTransactionIsolation(int) is called again (as documented in the javadoc I linked in my previous comment. Calling setTransactionIsolation will always use the transaction isolation configuration of the connection, and setTransactionParameters(TransactionParameterBuffer) doesn't modify that. If you want to modify the transaction isolation configuration of the connection, then you need to use .setTransactionParameters(int, TransactionParameterBuffer) or configure it in the URL.

@firebird-automations
Copy link
Author

Commented by: Attila Molnár (e_pluribus_unum)

OK Mark, thank you!
I managed to do an elegant workaround for this in my project.

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