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.)?
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