In another application I was struck by bad design: multiple threads execute an EnsureDatabaseSchemaExists() method concurrently, which looks basically like this:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'MyTable') AND type = N'U') BEGIN

    CREATE TABLE MyTable ( ... );


However, even if executed in a SERIALIZABLE transaction, this code does not seem to be thread-safe (i.e. the parallel code tries to create the table multiple times). Is there any chance to force the SELECT-statement to acquire a lock which prevents another thread to do the very same SELECT statement?

Is there a better pattern for multi-threaded-EnsureSchemaExists() methods?


2 Answers 2


You best bet is to use an explicit containing transaction and acquire a custom exclusive lock to protect the whole operation (SELECT and CREATE TABLE) using sp_getapplock. System objects do not honor isolation level requests and use locks in the same way as user tables, by design.

The race condition in the original code is that multiple threads can conclude the table does not exist before any thread gets as far as the CREATE TABLE statement.

  • 6
    +1 just make sure the applock wraps the SELECT check. Otherwise you'll introduce deadlocks. Ideally one would get the app lock in S mode, check, the upgrade to X, but that is tricky (to say the least...). The safest option is to acquire X, then do the entire DB schema deployment. It should be a rare op (eg. at app startup) so the X lock should not matter that much. Commented Mar 10, 2014 at 18:23

My recommendation would be to do a best effort try/catch. Handle the duplicate case explicitly, as appropriate, eg. ignore it...

The real question: why is DDL running on-demand, from multiple xacts? Normally upgrade and migration are a serious matter, handled in dedicated time windows... You do not want your migration (code-first?) to kick in unexpectedly, some of those update steps can take hours on a big table (size-of-data operations...)

  • 3
    The code is some kind of DatabaseLogger which creates its tables on-demand. No migration, no funny business. However, you're completely right. I'm going to refactor the code appropriately.
    – D.R.
    Commented Mar 10, 2014 at 18:12
  • 4
    Also consider that deployment/setup is perfectly OK to run on an elevated privilege context (eg. by an admin), but normal ops is not. Currently you are requiring CREATE TABLE grant for normal ops... Commented Mar 10, 2014 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.