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