what will happen during swapping table names for a highly used table production table?

Question

It's getting hard to update(lock) a master production table,

so we decide to create a import table, when copying data completed.

we swap name between import table and production table:

code as:

set DEADLOCK_PRIORITY 8;

exec sp_rename p_table, p_table_swap;
exec sp_rename p_table_Import, p_table;
exec sp_rename p_table_swap, p_table_Import;

the production table are being queried all the time (read only).

I want to know what will happen during the name swap.

The reading queries will be killed or just wait till swap complete?

Thanks

Answer 1

The reading queries will not be killed.

If a query is submitted to the processor before the rename command, assuming the queries don't have lock hints, they will complete and your rename command will be put on hold on a wait until the query processor thinks it's ok to process the rename command. The readers of the data in this instance will get the data from before the rename (old data) which makes sense.

If a query is submitted during or after the rename command is issued, those processes will be put on hold until the rename is complete and the query processor deems it ok to allow the submitted query to access the data.

The rename command will create a schema lock which essentially blocks everything from the table until it is completed. Now you could look into changing how your table is accessed. Using snapshot isolation in the reading queries could theoretically allow you to insert and update data while not disrupting the read queries: http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx