8

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.

6
  • 1
    If you're doing an INSERT/UPDATE/DELETE on both tables in a single transaction, you should be fine due to how locks work when it comes to exclusive locks, unless you/your users have made excessive use of the NOLOCK hint in queries Nov 29, 2017 at 20:51
  • That's true! But I want both objects to held by a single lock so no user can query either table during the maintenance. The exclusive lock taken by the INSERT/UPDATE/DELETE will only last for the duration of the change to a single table. I could add "WITH(HOLDLOCK)" but the second table could still be read from while the first table is being updated.
    – Sidawy
    Nov 29, 2017 at 20:55
  • But the users won't see any of the changed data until the transaction is committed to both tables (if they're in the same transaction), so users will only be accessing the original data, if any, during the update of both tables (assuming you're not allowing dirty reads). Nov 29, 2017 at 20:57
  • I just ran a test on my local instance of 2014 and the behavior is not working as you described. The exclusive update lock on the the second table is not being taken until the update on the first table is complete.
    – Sidawy
    Nov 29, 2017 at 21:03
  • Thats not what I was saying in the last comment. Anyways, Aaron explicitly answered your request so you should use his answer (though I think the locking is still overkill). Nov 29, 2017 at 21:12

2 Answers 2

12

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.

9
  • Thanks! This looks very promising. I will test this out first thing tomorrow!
    – Sidawy
    Nov 29, 2017 at 21:28
  • NOLOCK (and if you do, you're getting exactly what you asked for!)... funny how many people think this literally means do not take out locks. That made me laugh so hard I had to clean coffee off my screen. Thanks Aaron!
    – Namphibian
    Nov 29, 2017 at 21:59
  • @Namphibian To be fair, it was named horribly (like TIMESTAMP and RAISERROR). Nov 29, 2017 at 22:03
  • Agreed but never assume nothing since assumption is the fother of all muckups.
    – Namphibian
    Nov 29, 2017 at 22:34
  • 1
    UPDLOCK is compatible with S lock, and even an X lock will not prevent NOLOCK, SNAPSHOT or RCSI read. Nov 30, 2017 at 15:32
5

I'll begrudgingly jump on the lock bandwagon.

Were you to want to actually lock the database for your update, as your title says, you could put the database in single user mode and rest assured that only your connection could connect at all while you made updates:

ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
GO

And after your updates:

ALTER DATABASE whatever
SET MULTI_USER
GO

Be warned, however, that this can be tricky - for example SSMS routinely opens many connections so it will start to behave poorly in those mode. Your best bet is using sqlcmd in this mode.

4
  • 1
    Yeah locking the whole database is pretty heavy-handed, it's also hard to do if you have heavy activity in SQL Server Agent, for example. Somewhere in the middle might be doing a rename or schema transfer of the tables in a serializable transaction. Nov 29, 2017 at 21:47
  • Agreed. A bit too heavy handed for me as well. And point taken, I should have named this post "SQL SERVER 2014 - multi-object lock" but that didn't come to mind immediately.
    – Sidawy
    Nov 29, 2017 at 23:11
  • @AaronBertrand the rename was my initial instinct, but then I thought to go even lighter weight in the comments. Somehow I ended up at the other end with this answer Nov 30, 2017 at 15:58
  • Interesting alternative. Although, in practice, this is downtime.
    – magma
    Jul 6, 2020 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.