Synapse staging-optimized replace strategy causes deadlock

[jorritsandbrink]

dlt version

0.4.8

Describe the problem

This issue occurs whenever we are transferring the schema for a table from schema_x_staging to schema_x and concurrently run an insert statement (or probably any DML operation) against a table in schema_x. The transfer and insert statements end up stuck in Running and Suspended state, respectively. As per the link you send, the transfer statement locks the whole schema, resulting in a deadlock whenever an insert is ran against any other table in the schema. To make matters worse, Synapse doesn't seem to detect/resolve deadlocks properly: https://dba.stackexchange.com/questions/300929/how-are-deadlocks-detected-by-synapse

Full discussion: https://dlthub-community.slack.com/archives/C06EYJ2Q37C/p1712571682549229

Expected behavior

No response

Steps to reproduce

import dlt

os.environ["DESTINATION__REPLACE_STRATEGY"] = "staging-optimized"

p = pipeline("create_deadlock", destination="synapse", full_refresh=True)

@dlt.source
def tables():
    @dlt.resource(write_disposition="replace")
    def replace_table():
        yield {"id": 1}

    @dlt.resource(write_disposition="merge")
    def merge_table():
        yield {"id": 2}

    return replace_table, merge_table

p.run([tables().replace_table, tables().merge_table])

Operating system

Windows

Runtime environment

Local

Python version

3.8

dlt data source

No response

dlt destination

No response

Other deployment details

No response

Additional information

No response