Transaction not getting completed after commit in Azure SQL Data Warehouse

Question

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)");
   }           
}

Answer 1

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.