Lock CREATE TABLE

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 ( ... );

END

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?

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.

StackExchange Database Administrators Q#60590, answer score: 18