connection.commit() does not immediately commit the transaction to the Azure Synapse SQL pool

[lewischeng-ms]

Question

Hi JDBC team, I'm from Azure Data Factory team and recently we are working on a project where we use JDBC (8.4.0) to send some T-SQL to an Azure Synapse SQL pool and manage transactions by ourselves within a Spark application. Below is our typical pattern:

connection.setAutoCommit(false);
// do some DMLs
connection.commit();
// Reuse the connection to do a new transcation immediately after the commit().

We find after the commit(), the transaction is not immediately committed to Synapse. I searched and found a relevant issue #872 which seemd to focus on the issue of setAutoCommit() instead of commit(). Based on the information from that issue, currently I added a setAutoCommit(true) after the commit() as a workaround and it did implicitly do the commit.

connection.commit();
connection.setAutoCommit(true);

But the semantics looks very weird because a commit() does not actually commit the transaction but a setAutoCommit(true) does...

So here comes my question: is this by design or a known bug? What should I do (what function/method should be called) to guarantee that a transaction is committed? In our scenario, the order of transaction commit is quite important, we must know for sure a transaction is committed before we go to the next one.

BTW, we have a similar issue on Stackoverflow: https://stackoverflow.com/questions/48300627/transaction-not-getting-completed-after-commit-in-azure-sql-data-warehouse

Your suggestions are much appreciated. Thanks.

Relevant Issues and Pull Requests

#872

[peterbae]

Hi @lewischeng-ms, I'd like to look into this myself, but the team is busy with an official release that's scheduled for end of this month. We will look into this issue as soon as we have some bandwidth.

[peterbae]

As a note, I tried this with SQL Server and this behavior couldn't be reproduced, so this has to be related to the difference in Azure Synapse's behavior.

[lewischeng-ms]

@peterbae Thanks for the response. Yes, exactly. There is no issue with SQL Server or Azure SQL DB. As noted in the StackOverflow question, the issue only happens when we use mssql-jdbc to connect to Azure SQL DW (Synapse).

[lewischeng-ms]

Today I took a look into the JDBC source code myself and found the root cause.

First, my previous thought was incorrect.The issue was NOT that JDBC commit() does not commit the transaction. It does but it’s just that we cannot execute any DDL after commit() other we would get an error “111212;Operation cannot be performed within a transaction.” which misled me to think the previous transaction was not immediately committed.

The real cause is:
According to https://docs.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=sql-server-ver15#remarks, when we turn off implicit transaction (because we want to control transaction on our own), even a DDL like CREATE TABLE statement would automatically trigger a BEGIN TRANSACTION, which begins a user-defined transaction. As a note that, when we turn on implicit transaction, it’s NOT a user-defined transaction.
But according to https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-develop-transactions#limitations, Synapse SQL pool does not support any DDL within a user-defined transaction so any CREATE TABLE statement would fail.
As you can see, these two implicit automatic server behaviors are actually contrary. It’s not JDBC’s problem. You just can never issue any DDL when implicit transaction is turned off in Synapse SQL pool.

The solution is simple: we just turn on implicit transaction by conn.setAutoCommit(true) before executing any further DDL.