setAutocommit(False) creates a new transaction

[rooneyp]

Issue Details

We are migrating from the old Simba driver to the new v1.1.0 driver.

We discoved that new Sessions are created on every statement execution if autocommit=True
(We raised a ticket for clarification: #13787)

To work with this behaviour, we have tried to set 'conn.setAutocommit(False)' when we need a transaction spanning multiple separate statements.

What we have found is that the our existing separate SQL statements will no longer work as their first statement 'BEGIN TRANSACTION' fails
This is due to: when we set conn.setAutocommit(False), this causes a new transaction to be created. Thus we get an exception stating that a transaction is already opened.

Example

Given we have a JDBC connection created with enableSession=1. And that autoCommit is enabled by default/spec. As mentioned these separate statements will fail on the first one: 'BEGIN TRANSACTION;

---

BEGIN TRANSACTION;

CREATE TEMP TABLE _SESSION.MY_TMP_TABLE AS select * from MY_SRC_TABLE;

INSERT INTO MY_TARGET_TABLE SELECT * FROM _SESSION.MY_TMP_TABLE ;


COMMIT; 

---

Environment

v 1.1.0

Dependencies

v 1.1.0 shadowed/all-classifier jar

Reproducer

Required: Provide a reproducer and the steps needed to reproduce this issue locally. If unable to provide a reproducer, please provide code snippets to help reproduce the issue. One of the two sections below MUST be filled out.

1. A reproducer is the quickest method to resolve this issue. It could be a test case or a sample application. It is easier for us to troubleshoot the problem and to verify the solution.

Steps to reproduce:

Example

1. Enable Speech API
2. Upload a .mp4 file to GCS
3. ...
4. See {ERROR}

If unable to provide a reproducer, please provide a reason: ...

2. Provide as many code snippets as possible:

Example

try (InstancesClient instancesClient = InstancesClient.create()) {
  ...
}

Logs and Stack Trace

Required: Provide logs that showcase the error. This will help show the flow of the application and help narrow down the cause. Additionally, provide a stack trace of the error if possible.

The Java SDK has a troubleshooting guide for enabling logs. This contains information regarding client-server communication, request and response details, and logging in dependency libraries. If using this guide, please obfuscate any private information (bearer tokens, request and response params, etc).

Additionally, please provide a stack trace of the error seen:

Example

TransportContext.java:347|Fatal (CERTIFICATE_UNKNOWN): PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target (

"throwable" : {

sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

	at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:456)

	at sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:323)

...

Behavior

Optional: Any additional information about the behavior of the error is helpful to debug.

Behavioral Questions:

• When did the issue begin? Is this behavior related to any dependency version upgrade?
• Is this behavior flaky? Or is this consistently seen in production?
• Is this behavior related to the volume of data?

[lqiu96]

I think Simba drivers is for Bigquery-JDBC. Please correct me if that is wrong.

CC: @logachev @Neenu1995 @keshavdandeva