DML statement locks table in Azure SQL Data Warehouse after implicit transaction.

[odelpozog]

Driver version

6.2.2.jre8, 6.4.0.jre8, 7.1.2.jre8-preview

SQL Server version

Microsoft Azure SQL Data Warehouse - 10.0.9999.0
Oct 10 2018 00:19:18
Copyright (c) Microsoft Corporation

Client Operating System

Windows 10 64 bits

JAVA/JVM version

java version "1.8.0_181"
Java(TM) SE Runtime Environment (build 1.8.0_181-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.181-b13, mixed mode)

Table schema

CREATE TABLE [dbo].[test_lock]
(
	[id] [int] NOT NULL
)
WITH
(
	DISTRIBUTION = ROUND_ROBIN,
	CLUSTERED COLUMNSTORE INDEX
)

Problem description

Executing a DML statement after an implicit transaction locks the affected table. Locks are released when the connection is closed but if the connection is in a pool, it would not be closed being used.

1. Expected behaviour:
   As long as the connection autoCommit is true, the update statement should not lock the test_lock table.
2. Actual behaviour:
   The object Database.dbo.test_lock is locked by the update statement and the deletion cannot be executed.
3. Error message/stack trace:
   None
4. Any other details that can be helpful:
   Executing the query after the lock I get the following results:
   SELECT TOP ,l.[session_id] ,l.[type] ,l.[object_name] , r.[command] ,l.[acquire_time] ,r.[end_time] ,r.[status] ,l.[state] ,l.[priority] FROM [sys].[dm_pdw_lock_waits] l inner join [sys].[dm_pdw_exec_requests] r on l.request_id = r.request_id where l.[object_type] = 'OBJECT'

session_id	type	object_name	command	acquire_time	end_time	status	state	priority
SID00001	Shared	Database.sys.sp_executesql	exec [sp_executesql] @p1	date	date	Completed	Granted	0
SID00001	ExclusiveUpdate	Database.dbo.test_lock	UPDATE test_lock SET id = 9 WHERE id = 10	date	date	Completed	Granted	0
SID00002	Shared	Database.sys.sp_executesql	exec [sp_executesql] @p1	date	NULL	Running	Granted	0
SID00002	ExclusiveUpdate	Database.dbo.test_lock	DELETE FROM test_lock WHERE id > 8	NULL	NULL	Suspended	Queued	10

JDBC trace logs

Reproduction code

public class Main {
    private static final String INSERT_QUERY = "INSERT INTO test_lock(id) VALUES (10)";
    private static final String UPDATE_QUERY = "UPDATE test_lock SET id = 9 WHERE id =  10";
    private static final String DELETE_QUERY = "DELETE FROM test_lock WHERE id > 8";
    
    private static String driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static String jdbcURI = "jdbc:sqlserver://...database=Database";
    private static String user = "";
    private static String password = "";
    
    public static void main(String[] args) {
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try (Connection c1 = DriverManager.getConnection(jdbcURI, user, password);
             Connection c2 = DriverManager.getConnection(jdbcURI, user, password);) {
            c1.setAutoCommit(false);
            PreparedStatement ps = c1.prepareStatement(INSERT_QUERY);
            ps.executeUpdate();
            c1.setAutoCommit(true);
            ps = c1.prepareStatement(UPDATE_QUERY);
            ps.executeUpdate();

            PreparedStatement ps2 = c2.prepareStatement(DELETE_QUERY);
            ps2.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

[peterbae]

Hi @odelpozog, thanks for reporting this issue. I was able to reproduce the issue, but I'm trying to pin down why this problem is occurring. I see that the repro code works fine with SQL Server, and it's only locking to table in Azure DW - I'll let you know as soon as I have an update on this issue.

[peterbae]

Hi @odelpozog, I've looked into this issue a bit deeper, and it seems like this is not a JDBC driver issue, nor an issue with Azure DW - I believe this is the intended behavior.

First of all, I confirmed that this is not our driver's issue by performing the same actions (from your repro code) in Azure DW. I first ran this query in one Query Editor in the Azure portal:

set implicit_transactions on

INSERT INTO test_lock(id) VALUES (10);

set implicit_transactions off
UPDATE test_lock SET id = 9 WHERE id =  10;

-- timer is used here to make the connection last for ~20 seconds after running the queries.
DECLARE @timer int = 0;
WHILE (@timer) < 50000
BEGIN  
SET @timer = @timer+1;
END  

While the while loop is keeping this connection open, I ran this query in another Query Editor:

SELECT TOP 5 l.[session_id] ,l.[type] ,l.[object_name] , r.[command] ,l.[acquire_time] ,r.[end_time] ,r.[status] ,l.[state] ,l.[priority] FROM [sys].[dm_pdw_lock_waits] l inner join [sys].[dm_pdw_exec_requests] r on l.request_id = r.request_id where l.[object_type] = 'OBJECT'

Which showed that a lock was still on the test_lock table. Since this operation doesn't use our JDBC driver, it showed that this was not our driver's issue.

Afterwards, I found this part of the MSDN document from here:

OFF (SET AUTOCOMMIT)
SQL Data Warehouse automatically initiates a transaction when a transaction is not already in progress.

I could confirm this behavior by putting queries that SELECT @@TRANCOUNT, and saw that a transaction was always active when we had the implicit transaction mode ON. This is why the lock was placed on the test_lock table in your repro code - because by the time you call c1.setAutoCommit(true), there was already a transaction open, and the update query places the lock on the test_lock table (but doesn't commit).

To demonstrate this behavior, I provided a modified version of the code I provided previously below. This will correctly close the transaction (that Azure DW opens for us every time when implicit transaction mode is ON) before we run the update statement in implicit transaction mode OFF, and the test_lock table won't be locked during the while loop:

set implicit_transactions on

INSERT INTO test_lock(id) VALUES (10);

COMMIT TRAN;

set implicit_transactions off

COMMIT TRAN;
SELECT @@TRANCOUNT;
UPDATE test_lock SET id = 9 WHERE id =  10;

DECLARE @timer int = 0;
WHILE (@timer) < 50000
BEGIN  
SET @timer = @timer+1;
END  

I also confirmed that a Java version of the code I provided above works correctly (you'll have to execute COMMIT TRANS from a statement instead of calling c1.commit() after you set c1.setAutoCommit(true), since the JDBC API suggests that commit() should not be called when autocommit is set to true, so that c1.commit() will end up becoming a no-op).

I hope this answers your question. Please let me know if you had other concerns/questions.

[odelpozog]

Hi @peterbae , thank for your reply.

I have tested your suggestion about executing COMMIT TRANS after setting the auto-commit property to true and it almost works (it raises the exception 112006;Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.). Anyway, it indeed decreases the @@TRANCOUNT.

Executing my original java code again, I do think there is an issue with Azure SQL DW.

Java Statement	Azure SQLDW statements	@@TRANCOUNT	locks
Initial state		0
c1.setAutoCommit(false);	set implicit_transactions on	1
ps.executeUpdate() //insert	INSERT INTO test_lock(id) VALUES (10)	1	SharedUpdate on test_lock
c1.setAutoCommit(true);	IF @@TRANCOUNT > 0 COMMIT TRAN set implicit_transactions off COMMIT TRAN set implicit_transactions off	1
ps.executeUpdate(); //Update	UPDATE test_lock SET id = 9 WHERE id = 10	1	ExclusiveUpdate on test_lock
ps2.executeUpdate(); //Delete	DELETE FROM test_lock WHERE id > 8	-	ExclusiveUpdate on test_lock by both connections

The correspondence between the java statements and the Azure SQLDW statements seem correct, the auto-commit to true executes the COMMIT TRANS and it restores the value of implicit_transactions to off. But, the TRANCOUNT counter is still 1 after that.

According the JDBC setAutoCommit documentation:

If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions.
NOTE: If this method is called during a transaction and the auto-commit mode is changed, the transaction is committed.

IMHO with setAutoCommit(true) the current transaction has to be committed and the next executed statements have to be committed automatically, without locking any database object (TRANCOUNT should have been decreased to 0).

If I need to execute a COMMIT TRAN statement after the setAutoCommit(true) my code will depend on the specific database being used, losing the benefits of using JDBC interfaces.

Looking forward to your comments, regards.

[peterbae]

I think that's a good point. The problem right now is in SQLServerConnection::setAutoCommit - when setAutoCommit is called, currently we execute this command when we want to turn off implicit transaction:

IF @@TRANCOUNT > 0 COMMIT TRAN set implicit_transactions off 

This works fine for SQL Server, but for Azure DW, between the COMMIT TRAN and set implicit_transaction_off statements, the server will create a new transaction since the implicit transaction was on. This is what's causing the table to lock, and I think the driver is responsible for cleaning up that transaction.

I swapped the order of those two statements (it shouldn't affect anything other than closing the new transaction in DW), and it seems to work fine. I've created a Java 8 version of the newest driver that contains the change and I've attached it to this comment. Please give this jar a try and see if it now behaves the way you expect it to:

mssql-jdbc-7.1.3-github872.jre8-preview.jar.zip

If the jar works well for you and after further internal testing, I will create a PR that swaps the order of those two statements. Please let me know!

[odelpozog]

Hi @peterbae
I've tested the new driver and it works perfectly.
Thank you

[odelpozog]

Hi @peterbae
I've been testing the preview.jar and it's working fine so I guess that the internal testings are working as well :)

When is this fix expected to be part of an official release?
Thanks

[peterbae]

This fix is expected to be part of the next preview release, which is scheduled for the end of this month.

[cheenamalhotra]

Closing Issue as PR #881 merged.