3

I'm trying to produce a deadlock in Azure Synapse. I would have expected it to work much as on-prem SQL Server does given the documentation says it "applies to" Synapse. I've waited an inordinately long time (over 4 hours) yet deadlock detection has not killed either of my sessions.

Using SSMS connected to a Synapse dedicated pool at scale DW100c I create and populate two tables.

create table t1(c int);
create table t2(c int);

insert t1(c) select 99;
insert t2(c) select 99;

In two separate SSMS session I execute these statements

Session 1                   Session 2
------------------------    ------------------------
begin transaction           begin transaction

update t1 set c = 0;

                            update t2 set c = 0;

update t2 set c = 0;

                            update t1 set c = 7;

Running this against an instance of SQL Server 2019 on my laptop produces the expected 1205 error message after a few seconds. Run it against Synapse, however, and both sessions simply sit locked for hours. I can see the sessions in sys.dm_pdw_lock_waits as expected. A web search produces nothing Synapse specific. Neither do MS docs call out any peculiarities that I can see.

These four updates are the only activity on this instance during the test. There is no other concurrent load. I see the same behaviour whether the tables' distributions are defined as HASH(c) or ROUND ROBIN.

How are deadlocks detected and handled by Synapse?

Michael Green
  • 25,255
  • 13
  • 54
  • 100

1 Answers1

-1

Based on docs, SQL Server does not allow statements that are waiting for locks to be blocked by newer lock requests. SQL Server PDW (synampse after all) has not fully implemented this process. In SQL Server PDW, continuous requests for new shared locks can sometimes block a previous (but waiting) request for an exclusive lock. For example, an UPDATE statement (requiring an exclusive lock) can be blocked by shared locks that are granted for series of SELECT statements. To resolve a blocked process (identified by reviewing the sys.dm_pdw_waits DVM), stop submitting new requests until the exclusive lock has been satisfied.

https://learn.microsoft.com/en-us/sql/analytics-platform-system/locking-behavior?view=aps-pdw-2016-au7#Remarks

So it seems that you simply don't have to do two updates.

MBuschi
  • 4,835
  • 1
  • 6
  • 17