Best Practice Recommendations for JDBC Driver use from Hibernate?

[dzou]

Hi Knut,

Recently we've been asked by a user as to how they can access special Cloud Spanner features through Hibernate (such as stale reads, read-only transactions, partitioned DML, etc.) Many thanks for your help over email from a few days before.

We've made some progress with the solution you showed us, so for example, a user can start a read-only transaction like this:

    try (Session session = sessionFactory.openSession()) {
      session.beginTransaction();
      session.doWork(connection -> connection.setReadOnly(true));
      ... Do something ...
      session.getTransaction().commit();
    }

The tricky thing we noticed though is that Hibernate seems to re-use JDBC connections across sessions. So if a session sets the JDBC connection to read-only, they would have to undo that setting later on to get a read-write transaction again. Something like this:

    // Open a new session, set to read-only
    try (Session session = sessionFactory.openSession()) {
      session.beginTransaction();
      session.doWork(connection -> connection.setReadOnly(true));
      ... Do something ...
      session.getTransaction().commit();
    }

    // Open a new session - this may re-use the connection from the previous session so one must set readOnly back to false
    try (Session session = sessionFactory.openSession()) {
      session.beginTransaction();
      // Set this back to false
      session.doWork(connection -> connection.setReadOnly(false));
      ... Do something ...
      session.getTransaction().commit();
    }

We thought a new Session would be given a fresh JDBC connection but it doesn't seem to be the case in Hibernate. Sessions seem to poll from the same pool of JDBC connections in Hibernate.

So for example, we thought in the multi-threaded use-case it might not work because if there are multiple threads (maybe some use read-write, some use read-only, some use stale-reads, etc.) they wouldn't be able to run concurrently if new Hibernate Sessions are opened (via SessionFactory.openSession) use the same JDBC connection underneath and the threads try to modify the connection's state.

Just wanted to ask - is our analysis of the problem correct and how might we document to users the best way to access the Spanner-specific features?

I also started a PR with some sample code (for doing stale reads, read-only, etc.) here if you are curious: GoogleCloudPlatform/google-cloud-spanner-hibernate#225

cc/ @meltsufin

[dzou]

@meltsufin just told that connections are not shared by sessions when they are in use, so I think what I said about the multi-threaded use-case not working was wrong.

I guess we're just wondering if there's any hook into Hibernate that would allow us to reset the connection when it is returned to the pool (on a session.close())? I wasn't able to find a method to override in the Dialect to do this.

[olavloite]

@meltsufin just told that connections are not shared by sessions when they are in use, so I think what I said about the multi-threaded use-case not working was wrong.

I guess we're just wondering if there's any hook into Hibernate that would allow us to reset the connection when it is returned to the pool (on a session.close())? I wasn't able to find a method to override in the Dialect to do this.

No, as far as I know there is nothing in Hibernate for that. Depending on the connection pool that the application uses, the connection pool might have functionality for that. Another option for a client application would be to create a small wrapper around the connection pool to add some custom functionality in the getConnection and closeConnection methods.

I can take a look if there's something we could do in the JDBC driver to make it possible for an application to easily execute a temporary read-only transaction. So instead of having to set the connection in read-only mode, you should be able to execute something like BEGIN READ ONLY TRANSACTION, which only makes the current/next transaction read-only, and then automatically reverts to read/write when the connection is committed. I think that is the most common use case for this.

[olavloite]

@dzou I had forgotten, but there is actually already a possibility of temporarily setting a different transaction mode in the JDBC driver. I'll make that feature available in the CloudSpannerJdbcConnection interface, but it is already available through custom SQL statements:

    // Open a new session, set to read-only
    try (Session session = sessionFactory.openSession()) {
      session.beginTransaction();
      // Set the transaction mode to read-only only for this transaction.
      session.doWork(connection -> connection.execute("SET TRANSACTION READ ONLY"));
      session.doWork(connection -> connection.execute("SET READ_ONLY_STALENESS = 'EXACT_STALENESS 15s'"));
      ... Do something ...
      session.getTransaction().commit();
    }

[meltsufin]

@olavloite So, is connection.setReadOnly(true) persistent, and connection.execute("SET TRANSACTION READ ONLY")) scoped to the transaction?

[olavloite]

@olavloite So, is connection.setReadOnly(true) persistent, and connection.execute("SET TRANSACTION READ ONLY")) scoped to the transaction?

Yes, correct.

[dzou]

Thanks @olavloite , this helps a lot! I just used the SET TRANSACTION READ ONLY statement and it works great.

[dzou]

Thanks for the help and quick solution. Closing.