Mamta posted this summary of holdability behaviour for use in debugging this issue. Client should match the embedded behaviour.
Just to summarize, the holdability table will look the same for EmbedConnection as well as BrokeredConnection.
Global Transaction Local Transaction
JDK13 CLOSE_CURSORS_AT_COMMIT conn holdability (Default HOLD_CURSORS_OVER_COMMIT)
JDK14+ CLOSE_CURSORS_AT_COMMIT conn holdability (Default HOLD_CURSORS_OVER_COMMIT)
A Connection object will have it's holdability set to default which is HOLD_CURSORS_OVER_COMMIT. When the connection joins a global transaction, the holdability will get switched internally to CLOSE_CURSORS_AT_COMMIT. When the connection leaves the global transaction and becomes part of local transaction, the holdability is restored to what it was before entering the global transaction. It works this way in both jdk13 and jdk14+. One thing to keep in mind is that PreparedStatement get the holdability from where they are prepared and not where they are executed,
Start Local Transaction
Connection holdability (default - HOLD_CURSORS_OVER_COMMIT)
PreparedStatement (holdability will be HOLD_CURSORS_OVER_COMMIT)
ResultSet from PreparedStatement will have holdability as HOLD_CURSORS_OVER_COMMIT
Start Global Transaction
Connection holdability switches to CLOSE_CURSORS_AT_COMMIT
Another ResultSet from PreparedStatement will attempt to have the preparedStatement's holdability and that will cause exception because Global transactions do not support holdable cursors.
Exit Global Transaction and become part of Local Transaction
Connection holdability back to HOLD_CURSORS_OVER_COMMIT
I have come across one bug with jdk13 though, where the holdability is not getting restored back to HOLD_CURSORS_OVER_COMMIT when the connection becomes part of local transaction after leaving global transaction. I will enter a JIRA entry for it and start working on it.