How to prevent multiple threads from competing to create dbt_test__audit schema?

I inherited a project with a SQL Server backend that contains 364 models and 98 tests. 18 of the first 20 are built-in tests such as accepted_values and not_null. The models build successfully, but when I get to the tests I keep getting a database error:

('42S01', "[42S01] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'dbt_test__audit' in the database. (2714) (SQLMoreResults); [42S01] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]CREATE SCHEMA failed due to previous errors. (2759)")

I can see in dbt.log that multiple tests are trying to create the dbt_test__audit schema at the same time. Example:

Tests 1 (accepted_values_S...) and 2 (not_null_A...) start on threads 1 and 2 respectively (lines 2043 & 2044 at top of screenshot). Thread 1 begins executing accepted_values_S and opens a new connection first (line 2056) before thread 2 (line 2060), but thread 2 tries to create the dbt_test__audit schema before thread 1 (lines 2062 and 2064). Thread 2 is therefore able to successfully commit not_null_A test 2 on line 2065 and thread 1 is forced to rollback accepted_values_S test 1 on line 2066.

This is being orchestrated via a Powershell script that creates a new database at the beginning of the run using a build profile in profiles.yml, so the dbt_audit__test schema definitely doesn’t exist beforehand. I am not logging errors with the --store-failures flag, so I don’t particularly care whether the schema gets created at all.

I tried reducing the number of concurrent threads in profiles.yml from 4 to 2 (as in the example above), but that just caused the failure to happen in the first two tests instead of slightly farther downstream. I don’t want to take concurrency down to a single thread if I can avoid it.

How can I avoid this collision? Is there a configuration I’m missing somewhere? Do I need to force each test to create its own schema (e.g., dbt_test__1, dbt_test__2, etc.)?

Maybe tell them to hurry up with his solution?

Note: @Hannes originally posted this reply in Slack. It might not have transferred perfectly.

Interesting, thanks for sharing. I’m not sure that resolving deadlocks will solve my issue though? I’m not actually having deadlocks, just full-on CREATE SCHEMA collisions.

This comment on the related issues thread seems most pertinent:

Isn’t part of the problems with the temporary creation of tables in Test that different tests create the same temporary table? That’s how I read the get_relation part of it