Select statement waits even after applying NOLOCK

Question

In SQL Server 2012, I am explicitly locking a table as listed below:

Reference: How to explicitly lock a table in Microsoft SQL Server (looking for a hack - uncooperative client)

While this lock is happening, I am running a SELECT query with NOLOCK on that table. But the query is not returing the data untill I stop the Window 1 and wait for some more time.

Why isn't NOLOCK working as expected?

--Window 1

DECLARE @TranName VARCHAR(300)
SET @TranName = 'MyTran';

BEGIN TRANSACTION @TranName


DECLARE @Current INT
SET @Current = 0

DECLARE @LoopCount INT
SET @LoopCount = 1;

WHILE @Current < 1
BEGIN

       SET @LoopCount = @LoopCount+1;
       PRINT @LoopCount

       ALTER TABLE DBATCPH ADD LockTest INT
       ALTER TABLE DBATCPH DROP COLUMN LockTest 

       WAITFOR DELAY '00:01';



END

GO 

Go

--Window 2

   SELECT TOP 1 * FROM DBATCPH NOLOCK

If you issue an `ALTER TABLE ` command, SQL Server will acquire a `SCH-M` (schema modification) lock - and that is incompatible even with the `NOLOCK` command. No workaround, no way around it - you just need to know this and respect it. [See the TechNet docs on Lock Modes](http://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx) for a detailed discussion — marc_s, Dec 03 '13 at 21:30
@marc_s. So, what is the alterantive for locking that table explicitly? — LCJ, Dec 03 '13 at 21:32
For as long as that `SCH-M` lock is in place, there is nothing you can do. You have to commit (or rollback) that transaction first. — marc_s, Dec 03 '13 at 21:33
@marc_s. Any other lock (other than SCH-M lock) that we can explicitly impart to a table? This is to test a defect in our C# application. — LCJ, Dec 03 '13 at 21:36
Not that I would know of - the `SCH-M` is the classic case to disprove all those claims that using `WITH (NOLOCK)` everywhere avoids / gets around any locking - it does not do that ... — marc_s, Dec 03 '13 at 21:39

score 5 · Accepted Answer · answered Dec 03 '13 at 21:38

If you issue an ALTER TABLE command in a transaction, SQL Server will acquire a SCH-M (schema modification) lock - and that is incompatible even with SELECT statements that use the WITH (NOLOCK) query hint.

There's no workaround or trick or other query hint you can use, no way around it - you just need to know this and respect it. The SCH-M will be released once the transaction with the ALTER TABLE statement has been committed (or rolled back).

See the TechNet docs on Lock Modes for a detailed discussion – there are other cases (besides ALTER TABLE) when a SCH-M lock is acquired (e.g. when the table is being truncated).

The BU lock (Bulk Update Lock) can also prevent SELECT's from happening - it will however allow other transactions to also bulk load at the same time (but it doesn't allow anything else).

Select statement waits even after applying NOLOCK

1 Answers1