TABLOCKX not working

Question

I am playing with my SQL Server 2012 trying to get a hold of the locks. Based on a tutorial I saw, I tried to test obtaining an exclusive lock on a table so that no other query would be able to read information from it, until the transaction is not over, but its just not working. Even though it was working in the video, here is my query in the first window ：

use TSQL2012

BEGIN transaction

update tele with (TABLOCKX, holdlock) 
set cor = '12'

waitfor delay '00:05'
go

then in a second query window I simply tried:

select * from tele

and it worked fine, although on theory there should have been "exclusive" lock preventing that. Why is that happening? I tried also with

set transaction isolation level serializable on

and also without the delay but the select is always successful. Any ideas?

Answer 1

I've tried this on two different tables and can easily reproduce what you've found. Here is what is happening:

If Sql-Server is returning rows to the SELECT query before the table is updated then its VERY probable that at that moment, there are already locks on your tele table that are incompatible with an exclusive lock. For example, if there is another session somewhere that already has a reader lock on the table then your UPDATE statement will be SUSPENDED with a WAIT_TYPE of LCK_M_X, meaning that your UPDATE is blocked, waiting for locks that are incompatible with an exclusive lock to be released. Since all locks are incompatible with an exclusive table lock then when any other session is accessing the table with any kind of lock, your update statement will be suspended.

In a 3rd instance of SQL Management Studio, right click your server and open activity monitor. Filter for your database and login and re-run the experiment. If the table is something that applications use frequently and the SELECT runs before the UPDATE, then you will notice a LCK_M_X WAIT_TYPE on the UPDATE.

Try creating a new table that only you know about and re-run the experiment. It should work.