How can I ensure one-at-a-time processing in SQL Server?

I have a data-upload function that load some data into several tables and processes them. I want my users to be able to initiate two uploads at once (though this will be the exception rather than the rule), but in that instance I want the first upload to complete before the second upload begins. (The whole process should take around 20 seconds).

The reason for this is that if two uploads put data into the tables at the same time, then they'll interfere with one-another'开发者_开发知识库s data. (Side-note: I originally planned to use temp tables to achieve process isolation, but found I couldn't do that).

What's the best way to ensure that upload 2 waits until upload 1 is finished? I could create a table to use as a mutex and grab a lock on that table (or a known row) inside a transaction that wraps the whole process, but is there a cleaner or more efficient way to do it?

Make a serialisable sproc that issues a unique identifier and tag the uploads with that ID value. Then they can go into the same table. The uploader can then initiate a batch process tagged with that ID to merge the data into the destination.

I would recommend putting each upload in it's own transaction. This way they will not intererfere.

In all your upload routines request exclusive TABLOCK on each affected table. That way the second upload will wait until existing TABLOCK is released.

Transactions are supposed to provide isolation. Can you explain why they would not for your scenario?