JPA Compatibility / Support for Read-Only Transactions

[MartinHaeusler]

What?

This PR adds the ability to start a read-only transaction, i.e. it adds support for the JDBC setReadonly(boolean) method after opening a JDBC connection.

Why? When working with Spring and Hibernate, the call pattern perceived by the JDBC driver is either:

• create connection
• set readonly = true
• use connection to query data
• close

... or:

• create connection
• set readonly = false
• use connection to query & write data
• close

We therefore require the ability to set the read-only flag explicitly after the connection has been opened in order to be compliant with hibernate.

Furthermore, SQLite has a notion of "auto-upgrading" read-only transactions to read-write transactions. This can cause SQLITE_BUSY exceptions which are a pain to deal with in a JPA/Hibernate/Spring scenario. For example:

• open connection
• query data <--- this uses a read-only transaction in SQLite by default
• write data <--- this is risky as it promotes the transaction to read-write!
• commit

The approach taken here is:

• We open transactions on demand
• We allow setting readOnly if and only if no statement has been executed yet
• If we receive readOnly(false), then we quit out of our transaction, and open a new transaction with BEGIN IMMEDIATE. This forces a global lock on the database, preventing SQLITE_BUSY.

How? Some additional internal state management is required to make this work. The solution might not be ideal, but the best I could do with my knowledge of the codebase.

Pros & Cons?

Pro:

• Support for JPA / Hibernate / Spring Data
• Proactively prevents SQLITE_BUSY errors
• Other behaviour of the driver is unchanged, test suite runs smoothly
• PR was tested and used extensively

Con:

• As BEGIN IMMEDIATE is global, deadlocks between threads can occur. This usually is not that big of an issue as Spring heavily promotes transaction/connection reuse.
• Logic is a bit scattered in the code, perhaps somebody else can do this better.

[gotson]

@MartinHaeusler can you check the failing CI?

Also, is this still current, 3 years later? I'm trying to catch-up on the issues and PRs.

[johannespostler]

This is still relevant for us. We maintain our fork and update/rebase it every now and then. Merging this would be highly appreciated :)

On Wed, 27 Jul 2022, 18:26 Gauthier, @.***> wrote:

@MartinHaeusler https://github.com/MartinHaeusler can you check the failing CI?

Also, is this still current, 3 years later? I'm trying to catch-up on the issues and PRs.

— Reply to this email directly, view it on GitHub https://github.com/xerial/sqlite-jdbc/pull/432#issuecomment-1197515983, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAL63TZM3FGCIHXHVVLO4YLVWHHT7ANCNFSM4HY76XMA . You are receiving this because you are subscribed to this thread.Message ID: @.***>

[gotson]

This is still relevant for us. We maintain our fork and update/rebase it every now and then. Merging this would be highly appreciated :)

Thanks for confirming. The fact that you use this in production for 3 years gives some credit. I recently looked at the transaction model, and i do agree in principle on your implementation.

[gotson]

Conflicts will need to be resolved and CI to pass before i can review. Thanks.