SQL Server 2014 – Database Level Locking

lockingsql serversql server 2014

I have a production instance of SQL Server 2014 that I need to do some light maintenance on.

Essentially, I need to replace the contents of two entire tables within a single transaction. I want to prevent anyone from querying either table while the data change is going on. The tables are small and I expect the operation to take less than a couple seconds.

Unfortunately, I don't have the benefit of scheduled downtime for this.

So the question is how can I take a lock on multiple objects at once – or even the entire database?

Ideally I could simply take a database level lock, make the changes, and release the lock but that doesn't seem possible in SQL Server 2014.

Best Answer

You can use a query with UPDLOCK first, to protect you from everything throughout the transaction (except dirty reads).

It can't ever be a single lock because a single lock can't span objects. But I still believe this accomplishes what you're after, as long as you don't have queries with NOLOCK (and if you do, you're getting exactly what you asked for!).

BEGIN TRANSACTION;

SELECT pkcol FROM dbo.foo WITH (UPDLOCK, HOLDLOCK)
UNION ALL
SELECT pkcol FROM dbo.bar WITH (UPDLOCK, HOLDLOCK);

-- do other stuff

UPDATE dbo.foo SET ...;
UPDATE dbo.bar SET ...;

-- do other stuff

-- default isolation level users will be blocked until:
COMMIT TRANSACTION;

It's possible that with NOLOCK a user could sneak in in the middle and query from the two tables and get data from the first table after its update and the second table before its update. But again, that is what you get when you allow NOLOCK.

And it is also possible that at that some point a user under default isolation level could query bar and see the old value, but they'd block on foo.

Related Question