Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Mar 10 at 17:58

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 15 down vote accepted

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.

share|improve this answer
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. –  Remus Rusanu Mar 10 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...)

share|improve this answer
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. Mar 10 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... –  Remus Rusanu Mar 10 at 18:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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