How to Lock a table on purpose without returning results?
Here 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?
What is sql server lock table?
The LOCK TABLE statement allows you to explicitly acquire a shared or exclusive table lock on the specified table. The table lock lasts until the end of the current transaction. To lock a table, you must either be the database owner or the table owner.
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;