Why don't locks work?

Question

SQL Server

I open two SSMS windows. In the first I make a blocking (as I assume) call

begin tran
SELECT TOP (1000) *
  FROM SomeTable With (TABLOCKX) -- Пробовал TABLOCKX HOLDLOCK, LOCKX, результат тот же
waitfor delay '00:00:15'
commit tran

In the second I read from the same table

SELECT TOP (1000) *
  FROM SomeTable

And the reading happens immediately, without any delays, i.e. no blocking occurs. Where is the error and what should be done to block all operations for the duration of the transaction?

Answer 1

I think the matter is in "READ_COMMITTED_SNAPSHOT = ON" on the tested database. When this option is enabled, MSSQL runs in versioner emulation rather than blocker emulation.
Try this before the test:

ALTER DATABASE [ServerTest] SET ALLOW_SNAPSHOT_ISOLATION off; 
ALTER DATABASE [ServerTest] SET READ_COMMITTED_SNAPSHOT off

PS: Info for reading
UPD: If there is a need to block lines with RCSI enabled, you need to add the "WITH (UPDLOCK)" hint,
i.e.:

begin tran
SELECT TOP (1000) *
  FROM SomeTable With (UPDLOCK) 
waitfor delay '00:00:15'
commit tran

and

SELECT TOP (1000) *
  FROM SomeTable  WITH (UPDLOCK)

Answer 2

You need to use two table hints simultaneously
WITH(TABLOCKX, HOLDLOCK)
TABLOCKX - exclusively locks the table, HOLDLOCK - turns on the SERIALIZABLE mode, which includes the following:
Queries cannot read data that has been modified but not yet committed by other transactions.
Other transactions cannot modify data that has been read by the current transaction until the current transaction ends.
Other transactions cannot insert new rows with key values ​​that fall within the range of keys read by any queries in the current transaction until the current transaction ends.
https://docs.microsoft.com/en-us/sql/t-sql/stateme...