Transaction management with Spring and Jaybird

[shalamyansky]

I use Java 8 with Spring JPA and Jaybird 4.0.1.
I need to set transaction parameters to

isc_tpb_read_committed

isc_tpb_rec_version

isc_tpb_nowait

isc_tpb_read_write

or maybe another set.

For annotation @Transactional I see how to set transaction isolation level and ReadOnly property, but how can I set nowait and no_rec_version values?

Here I found method to solve the task at db connection level:

spring.datasource.url:jdbc:firebirdsql:native://atlant-smart.vpn/beagle?encoding=WIN1251&TRANSACTION_READ_COMMITTED=isc_tpb_read_committed,isc_tpb_rec_version,isc_tpb_write,isc_tpb_nowait

It works. But what I have to do to set parameters no_rec_version and nowait for each transaction separately?

[Mark Rotteveel]

On 2020-11-29 16:38, shalamyansky wrote:
> I use Java 8 with Spring JPA and Jaybird 4.0.1.
> I need to set transaction parameters to
>
> isc_tpb_read_committed
> isc_tpb_rec_version
> isc_tpb_nowait
> isc_tpb_read_write
>
> or maybe another set.
>
> For annotation @Transactional I see how to set transaction isolation
> level and ReadOnly property, but how can I set nowait and
> no_rec_version values?
>

> Here [1] I found method to solve the task at db connection level:

>
> spring.datasource.url:jdbc:firebirdsql:native://atlant-smart.vpn/beagle?encoding=WIN1251&TRANSACTION_READ_COMMITTED=isc_tpb_read_committed,isc_tpb_rec_version,isc_tpb_write,isc_tpb_nowait
>
> It works. But what I have to do to set parameters no_rec_version and
> nowait for each transaction separately?

You can't, not if you want to handle this through @Transactional. Then,
the only option is to configure the isolation level for the entire
connection, and specify the overridden transaction isolation level.

The only way you can handle this for a specific transaction (or at
least, until the isolation level is changed) is to use the
Jaybird-specific extension to the JDBC API, as covered in
https://firebirdsql.github.io/jaybird-manual/jaybird_manual.html#transactions-tpb.
Given this is a non-standard extension, you cannot use it from
@Transactional.

Mark

[shalamyansky]

Thanks Mark!

This example shows how to configure TPB for db connect through code not via config.

So please hint me how to start a transaction and set the desired TPB for it in the context of my controller. Sorry, I'm not so familiar with JDBC and Java.

I expect something like this code:

Transaction transaction = connection.getTransaction();

transaction.setTpb( ... );

transaction.start();

myRepository.doSomething();

transaction.commit();

Is it possible?

[Mark Rotteveel]

On 01-12-2020 16:16, shalamyansky wrote:
> This example shows how to configure TPB for db connect through code not
> via config.

Wasn't that what you asked for? You showed that you had already found
how to change it through config.

> So please hint me how to start a transaction and set the desired TPB for
> it in the context of my controller. Sorry, I'm not so familiar with JDBC
> and Java.
>
> I expect something like this code:
>
> Transaction transaction = connection.getTransaction();
> transaction.setTpb( ... );
> transaction.start();
> myRepository.doSomething();
> transaction.commit();
>
> Is it possible?

JDBC doesn't offer explicit transactions, so there is no such mechanism.
JDBC only provides transactions that are implicitly started when needed.
The code I show does exactly what you want, assuming there is no current
active transaction (the transaction config set will take effect from the
next transaction started, until the config is changed or the isolation
level is changed).

So what you can do is

Connection connection = ....
// ensure no active transaction
connection.commit();
FirebirdConnection fbConnection =
connection.unwrap(FirebirdConnection.class);
TransactionParameterBuffer tpb =
fbConnection.createTransactionParameterBuffer();
tpb.addArgument(TransactionParameterBuffer.READ_COMMITTED);
tpb.addArgument(TransactionParameterBuffer.REC_VERSION);
tpb.addArgument(TransactionParameterBuffer.WRITE);
tpb.addArgument(TransactionParameterBuffer.NO_WAIT);
connection.setTransactionParameterBuffer(tpb);

And the next transaction that is started will use this transaction
configuration (assuming there is no call to setTransactionIsolation
which would reset the transaction config to the one explicitly (or
default) configured for the isolation level)

However, I'm not sure how well this will interact with Spring Data JPA
related code. I don't normally fiddle with these kind of low-level stuff
when using Spring Data.

Mark
--
Mark Rotteveel

[shalamyansky]

Thanks! This is what I need to solve the task and start figuring out low-level stuff.