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?