SET IMPLICIT_TRANSACTIONS Behavior On Azure SQL Data Warehouse and APS

Статья
02/20/2018

Working with transactions in Azure SQL Data Warehouse (ADW) and the Analytics Platform System (APS, aka PDW) is a bit different than one would expect. Though most of us tend to operate under the default behavior with IMPLICIT_TRANSACTIONS OFF, developers that interact with ADW/APS using other languages may find transaction handling using their language constructs to not work as expected.

Before we begin . . .

Let's first consider the following sample Java program:

[java]import java.sql.*;

public class TrxTest {
public static void main(String[] args) {
// Create a variable for the connection string.
//String connectionUrl = "jdbc:sqlserver://localhost;instanceName=SQL2016;databaseName=SmpHotnessDB;user=SmpUser;password=*****";
String connectionUrl = "jdbc:sqlserver://MppHotnessServer.database.windows.net;databaseName=MppHotnessDB;user=MppUser;password=*****";

// Declare the JDBC objects.
Connection connection = null;
Statement statement = null;
ResultSet rs = null;

try {
// Establish the connection.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
connection = DriverManager.getConnection(connectionUrl);

statement = connection.createStatement();
statement.execute("CREATE TABLE dbo.transaction_001 (id INT)");
statement.execute("INSERT INTO dbo.transaction_001 (id) VALUES (1)");

// Try one transaction
// BEGIN TRANSACTION
connection.setAutoCommit(false);
statement.execute("DELETE FROM dbo.transaction_001 WHERE id = 1");
statement.execute("INSERT INTO dbo.transaction_001 (id) VALUES (10)");
connection.commit();
connection.setAutoCommit(true);
// END TRANSACTION

// Following statement fails with "Operation cannot be performed within a transaction"
statement.execute("CREATE TABLE dbo.transaction_001_new (id INT)");
}

// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
}
finally {
if (statement != null) try { statement.close(); } catch(Exception e) {}
if (connection != null) try { connection.close(); } catch(Exception e) {}
}
}
}[/java]

Assuming you have both an SMP instance and an MPP (ADW/APS) instance handy, you can run the above and switch between the two platforms to test the differences. When running against an SMP instance, everything works as one would expect. However, when executing against ADW/APS, you receive the following error:

What's Happenin'

The error you receive from ADW/APS is due to an enforced restriction on these platforms. You are likely familiar with (and perhaps frustrated by) the fact that DDL operations can't be wrapped in a transaction. Regardless, this error is admittedly confusing because the setAutoCommit(true) method should have committed any open transactions, leaving the open transaction count at 0. Though SMP cleans up when the connection.setAutoCommit(true) statement is issued, ADW/APS doesn't (exactly). In short, this is because ADW/APS will immediately open a transaction following a SET IMPLICIT_TRANSACTIONS ON statement; more than this, ADW/APS will start a new transaction even after a COMMIT TRAN is issued when IMPLICIT_TRANSACTIONS is ON. As such, ADW/APS perpetually maintains a transaction count of (at least) 1 while IMPLICIT_TRANSACTIONS is ON. To contrast, SMP won't issue a new transaction until a new DML or DDL statement is issued after the COMMIT TRAN statement. You can test this behavior by issuing the following statements (which are effectively what the Java program sends to the database with some insert SELECT @@TRANCOUNT statements) one at a time against each different platform:

[sql]SELECT @@TRANCOUNT

CREATE TABLE dbo.transaction_001 (id INT)
INSERT INTO dbo.transaction_001 (id) VALUES (1)
SELECT @@TRANCOUNT

set implicit_transactions on;
SELECT @@TRANCOUNT

DELETE FROM dbo.transaction_001 WHERE id = 1
INSERT INTO dbo.transaction_001 (id) VALUES (10)
SELECT @@TRANCOUNT

IF @@TRANCOUNT > 0 COMMIT TRAN
SELECT @@TRANCOUNT

set implicit_transactions off
SELECT @@TRANCOUNT

CREATE TABLE dbo.transaction_001_new (id INT)
SELECT @@TRANCOUNT
[/sql]

Make It Right

This difference in behavior complicates existing coding patterns against SQL Server SMP when applied to ADW/APS because a manual COMMIT TRAN must be issued in order to properly close out a transaction even after setAutoCommit(true) is executed:

[java]
// BEGIN TRANSACTION
connection.setAutoCommit(false);
statement.execute("DELETE FROM dbo.transaction_001 WHERE id = 1");
statement.execute("INSERT INTO dbo.transaction_001 (id) VALUES (10)");
connection.commit();
connection.setAutoCommit(true);
statement.execute("if @@trancount > 0 commit tran");
// END TRANSACTION
[/java]

When this is in place, IMPLICIT_TRANSACTIONS is OFF and all open transactions have been properly closed.

Before We're done - IMHO . . .

One could argue that the behavior of setAutoCommit(true) could be improved to issue the following:
[sql]IF @@TRANCOUNT > 0 COMMIT TRAN
set implicit_transactions off
IF @@TRANCOUNT > 0 COMMIT TRAN
[/sql]

Instead of:

[sql]IF @@TRANCOUNT > 0 COMMIT TRAN
IF @@TRANCOUNT > 0 COMMIT TRAN
set implicit_transactions off
[/sql]

Alas, it doesn't . . . so you have to account for this. And this is the point: you can't necessarily always control how a given package/library/API implements its transaction control with a database; such is the case in the above scenario. However, you can better control transactions in ADW/APS if you:

Leave IMPLICIT_TRANSACTIONS = OFF (aka, setAutoCommit(true)) and
Utilize stored procedures to manage transactions rather than do it from another language's implementation

Helpful Links

/en-us/sql/connect/jdbc/reference/setautocommit-method-sqlserverconnection /en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql /en-us/sql/connect/jdbc/building-the-connection-url

Thanks to my technical reviewers: Allan Miller & Charl Roux

Поделиться через