This PR fixes a common source of SQL deadlocks by removing foreign key constraints from the core tables. Not only does this resolve deadlocks, but some tests are showing significant performance improvements, helping justify this potentially controversial change. As an example, a 2,000 sub-orchestration stress test went from taking >150 seconds to <40 seconds after this change. Comments have been added to the SQL schema file explaining why there are no FK constraints.
Data consistency will be enforced through careful use of transactions across the various stored procedures.
This PR fixes a common source of SQL deadlocks by removing foreign key constraints from the core tables. Not only does this resolve deadlocks, but some tests are showing significant performance improvements, helping justify this potentially controversial change. As an example, a 2,000 sub-orchestration stress test went from taking >150 seconds to <40 seconds after this change. Comments have been added to the SQL schema file explaining why there are no FK constraints.
Data consistency will be enforced through careful use of transactions across the various stored procedures.
Resolves #45 Resolves #23
Also: