3

In an update script I'm locking several tables.

BEGIN TRANSACTION
    SELECT Top 0 Null FROM TableA with (holdlock, tablockx)
    SELECT Top 0 Null FROM TableB with (holdlock, tablockx)
    ...

I want to suppress results for it in order to focus on real script results. Is there an elegant way to xlock a table without getting a result set?

empty result in ssms

8

There may be other ways, but this technique seems simple enough:

SET NOCOUNT ON
BEGIN TRANSACTION
declare @DummyVariable INT
SET @DummyVariable = (SELECT  Top 0 Null FROM Test1 with (holdlock, tablockx))
  • 1
    short and simple. Even shorter is SELECT @noResult = Null FROM ... – VV5198722 Nov 17 '16 at 13:20
7

You could update some records with a where clause that never matches like this:

Test setup:

CREATE TABLE locktest (id int, sometext nvarchar(50));

INSERT INTO locktest (id, sometext) VALUES
(1, 'dqsmfkqdsfjm'),
(2, 'qmsdfmdj'), 
(3, 'qkfjmsdfjk');

If you then, in one query window execute this:

BEGIN TRAN;
UPDATE locktest WITH (tablockx)  SET id=null WHERE 1=2;

You will see that a SELECTin another query window is blocked until you execute

COMMIT TRAN;

This would lock the table without returning a result set. If you want to suppress the 0 rows affected message you could add

SET NOCOUNT ON;

If you only want to block updates but let readers still read the table you could use:

UPDATE locktest WITH (tablock, updlock)  SET id=null WHERE 1=2;
  • I think it's misleading if you read UPDATE in SQL code in order to acquire an X-lock. – VV5198722 Nov 18 '16 at 7:18
1

The easiest way is to use sp_getapplock but you're forcing serializable concurrency when you do this for exclusive access and it's a terrible idea.

sp_getapplock @Resource = 'MyTable', @LockMode = 'Exclusive'; 

However, this will not take an actual lock on the resource and is an application logical lock only. When I read the OPs statement I did not think that actually locking the tables would be a good idea as there is a script or application that is attempting to do something. Since locking the tables will effectively cause serialized access to the tables - or worse, result in deadlocks in the script effectively denying everyone access - I gave this application lock example as a possible alternative. This way the application lock can be checked and/or taken for the parts in the script that were necessary but leaving the system in an otherwise functioning state.

If the OP truly does need to lock tables and provide serialized access, it may be worth changing the isolation level to serializable or re-thinking how the script or application functions.

  • AFAIK sp_getapplock @Resource = 'MyTable' acquires a lock with name MyTable but doesn't lock the table of this name. – VV5198722 Nov 18 '16 at 7:23
  • @VV5198722 You are correct, let me update my answer to be more explicit in what I meant. – Sean Gallardy Nov 18 '16 at 14:29
  • My script changes database schema and reorganizes data. It runs once per database. Therefore table X-lock is the right way IMHO. But that's another point. My question above was about the result sets. sp_getapplock doesn't create any result at least. – VV5198722 Nov 18 '16 at 15:00
  • 1
    If it changes schemas... that's going to require a schema-modification lock which isn't compatible with any other locks, so I'm not sure why the xlock? @VV5198722 – Sean Gallardy Nov 18 '16 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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