CODEWITHSUNDEEP
azureazure-synapse
subash
subash

Reputation: 51

Transaction not getting completed after commit in Azure SQL Data Warehouse

I am trying out transactions using JDBC in Azure SQL Data Warehouse. The transaction is successfully processed, but after the transaction, DDL command fails with error Operation cannot be performed within a transaction.

Here is the what I am trying to do.

connection.createStatement().execute("CREATE TABLE " + schema + ".transaction_table (id INT)");
connection.createStatement().execute("INSERT INTO " + schema + ".transaction_table (id) VALUES (1)");
connection.createStatement().execute("INSERT INTO " + schema + ".transaction_table (id) VALUES (2)");

// Transaction starts
connection.setAutoCommit(false);
connection.createStatement().execute("DELETE FROM " + schema + ".transaction_table WHERE id = 2");
connection.createStatement().execute("INSERT INTO " + schema + ".transaction_table (id) VALUES (10)");
connection.commit();
connection.setAutoCommit(true);
// Transaction ends

// Next DDL command to succeed, but it does not
connectiom.createStatement().execute("CREATE TABLE " + schema + ".transaction_table_new (id INT)");
// Fails with `Operation cannot be performed within a transaction`

So, how can we close the transaction in Azure SQL Data Warehouse.

I tried to do it like this.

try {
     // This fails
     connection.createStatement().execute("CREATE TABLE " + schema + ".transaction_table_new (id INT)");
} catch (SQLServerException e) {
   if (e.getMessage().contains("Operation cannot be performed within a transaction")) {
        // This succeeds
        // Somehow the transaction was closed, may be because of the exception
        connection.createStatement().execute("CREATE TABLE " + schema + ".transaction_table_new "(id INT)");
   }           
}

Upvotes: 3

Views: 5077

Answers (2)

subash
subash

Reputation: 51

It looks like we have to end the transaction manually.

It looks like this

connection.setAutoCommit(false);
// Transaction statement 1
// Transaction statement 2
connection.commit();
connection.setAutoCommit(true);
connection.createStatement().execute("IF @@TRANCOUNT > 0 COMMIT TRAN");

This is because, for Azure SQL Data Warehouse, jdbc connection.commit() doesn’t appear to always issue the COMMIT. It keeps track of transactions it’s managing and decides to be “smart” about what it sends. So manual COMMIT TRAN is executed to close all the open transactions before executing any DDL commands.

This is strange as we don't have to do this for other warehouses or databases, but it works. And, this is not documented.

Upvotes: 1

Matt Usher
Matt Usher

Reputation: 1325

SQL Data Warehouse expects the CREATE TABLE statement to be run outside of a transaction. By setting the connection.setAutoCommit to true, you are forcing Java to run the execute within a transaction. I'm a bit weak on Java (it's been a while) but you should be able to run the second DDL statement by simply commenting out the setAutoCommit(true) line. This will leave the JDBC driver in an execute mode only and not run the execute() operation within a transaction.

Upvotes: 3

Related Questions