creating a table if it doesn't exist

Question

Given the following:

if object_id('MyTable') is null create table MyTable( myColumn int )

Is it not possible that two separate callers could both evaluate object_id('MyTable') as null and so both attempt to create the table.

Obviously one of the two callers in that scenario would fail, but ideally no caller should fail, rather one should block and the other should create the table, then the blocked caller will see object_id('MyTable') as not null and proceed.

On what can I apply exclusive lock, such that I'm not locking more than is absolutely required?

Answer 1

I don't think, you should be worrying about this.

DDL statements don't run under a transaction. Also, the 2nd caller will fail, if the table already was created by a call from the 1st caller.

Answer 2

After your initial check, use a try catch when creating the table, and if the error is that the table already exists, proceed, if not, you have a bigger problem

Answer 3

I don't allow users to create tables. In general that's a bad practice. If they need to insert data, the table is already there. If you are worried about two people creating the same table are you also worried about whether their data is crossing? I don't know what your proc does but if it deos something like delte the records if the table exists and then insert, then you could have strange reults if two users were on at a the same time. In general though, if you are needing to creat ea table at run time , it is usually a sign that your design needs work.

Answer 4

Usually CREATE TABLE is run from setup and installation scripts and is unreasonable to expect installation scripts to allow for concurrent installation from separate connections.

I recommend you use a session scopped app lock acquired at the beginning of your install/upgrade procedure, see sp_getapplock.