I need some guidance on locking in Azure SQL. I have a number of tables being extracted to Azure, first to staging and then 'upserted' to a base table. There is a table used by all the sp to keep track of the delta loads - DL_LOAD_TIMESTAMP_MASTER (MASTER) which has a pk DL_LOAD_TIMESTAMP and an index on OLTPSOURCE.
When the delta hits the staging it creates a corresponding record in MASTER for that timestamp and source table. An sp then merges the delta from staging into the base table. They all run in parallel and because they're small & quick there is no problem. The upsert sp use a transaction with the update to MASTER in it. (The MASTER is also the target of fk constraints)
Deltas are all upserted or rolled back serially in a WHILE loop. So far it has all proven to be very reliable.
When I rolled back the entire history of 1 source table (from the base table) it blocked all other unrelated upserts from updating MASTER.
I can put WITH (ROWLOCK) in the upsert but for rollback I really want to lock all but only records for the relevant source table in MASTER (otherwise there is the potential for an upsert to execute at the same time). Possibly using a KEY RANGE lock somehow?
Your thoughts & suggestions would be appreciated.