Deadlocks on target server when more than 1 dbt thread

[IliasIB]

Expected Behaviour

dbt can run many tests in parallel without causing database deadlocks.
dbt can query information_schema and do DDL on separate threads without deadlocks.

Actual Behaviour

Intermittent deadlocks. So far only observed on dbt build (with tests) and dbt test.

('40001', '[40001] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Transaction (Process ID 237) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (1205) (SQLExecDirectW)')

Steps to reproduce

dbt test --threads 10

Configuration

A set of small generic dbt tests - I have ~50 tests
dbt 1.4.6
Fabric Warehouse

Possible causes

This issue seems to be similar to the one for dbt-sqlserver.

Other information

Current workaround: limit dbt threads to 1.

Just like in the original issue, the workaround seems to function correctly and dbt run has no issues.

[IlonaTag]

I've experienced the same situation when I run dbt run on a fabric warehouse with threads > 1. It works fine first but after having processed 20-30 models successfully, the process suddenly hangs completely in a way that the warehouse cannot be accessed completely. I have to manually kill the hanging/deadlocked insert statements to be able to proceed. I wonder how a deadlock situation can occur because all models are certainly different data objects.

The only workaround seems to be when I set threads to 1.

I've tested it also in combination with Airflow where I setup single dbt run processes for each model separately. Makes no difference, as soon as I run more than one task in parallel, it works for 20-30 models, than hangs completely. After the first hang, unfortunately, parallelism does not work at all anymore.

I also experienced quite a poor performance, especially with incremental models when there are 0 changes. In this case, dbt run seems to run forever. Hard to understand because in my scenario I use Adventure Works database which does only contain quite small volumes of data.

A solution is highly appreciated.

[IlonaTag]

In the meantime, the situation has slightly changed. I can run now a complete dbt run (with threads=10) within dbt core without any problem and good performance, the deadlocks only occur when I process dbt run separately for each single dbt model in parallel via Apache Airflow (with a parallelism of 26). The good thing: The retries work, it can "heal itself":

[IlonaTag]

If I lower the degree of parallelism also to 10 or less in Airflow, it runs a little bit more stable (less number of deadlocks). Performance btw is significantly worse as with one single dbt run command.