NoLock was blocked

For nolock in SQL Server, it's equivalent to READUNCOMMITTED transaction isolation level. It will not be blocked by other locks event exclusive locks but SCH-M (schema modification lock) because it does put S lock on DB and SCH-S(stability) lock on table.

Today, when I issued a query like "select * from test nolock", it was blocked by an update statement from another connection. I was so confused and began to doubt if my knowledge is solid. :(
Then after a while I realized that for nolock table hint, it does not require WITH keyword, but it needs to be enclosed in brackets\parentheses. In the experimented statement, the nolock was treated as table alias. after correcting it to be "select * from test (nolock)", it executed as expected.

A subtle mistake to me today.

Blocking and detection

What is blocking and how would you troubleshoot it?

Blocking occurs when a process has acquired lock on a set of rows, and another process is trying to acquire a lock on the same set of rows. In such a case, the other process has to wait until the first process finishes its job and releases the lock on the above said rows.

• Use sp_lock procedure to see type of locks acquired by various sessions on the server to find the cause of blocking.
• Problem is hinted by the WAIT status is a lot of rows that are returned as an output of sp_lock stored procedure execution.
• Use sp_who and sp_who2 to return more columns to get more information around the blocking.
• Use DBCC INPUTBUFFER (spid).This will show the last 128 characters of the last T-SQL statement executed from connection referred through spid. This way one can identify the stored procedure or application module that caused blocking.
• To resolve blocking, you can disconnect the connection causing the blocking using KILL command. If this does not solve the problem permanently, then rewrite the stored procedure/module causing the block more efficiently.

sp_lock

sp_who2

select * from master..sysprocesses

(

select spid,cmd,program_name,blocked,waittime,waitresource,lastwaittype from master..sysprocesses WHERE spid in (66,64) and dbid=7

)

to view the last issued statement, select * from ::fn_get_sql(sql handle returned from sysprocesses)