Select statement waits even after applying NOLOCK
sql-server
Solution
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).
Problem
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 ```