Tag : block


NOLOCK table hint 9

A lot of people use NOLOCK table hint in their SELECT queries, some of them are expert and well aware of its pros and cons, but some are not. So, In this post we are going to focus on this table hint and its impact.

What is NOLOCK

NOLOCK and READUNCOMMITTED hints used with SELECT queries to get the data without issuing shared lock (which prevents modification of data by other transactions during read operation) on the data, and also allows to read the data acquired with exclusive lock by other transactions. NOLOCK hint is equivalent to READUNCOMMITTED isolation level which is least restrictive in SQL Server. It does not restrict the dirty reads and increases the concurrency by reducing blocking.

It holds a Sch-S (Schema stability) lock during its execution scope which blocks the transactions requesting for Sch-M (Schema modification) lock, required by DDL operations. Reverse is also true … More


Locking, Blocking and Deadlocking in sql server 2

Lock vs Block vs Deadlock

Have you ever asked to differentiate lock, block and deadlock in sql server during your interview session? In this article we are going to differentiate these frequently confused terms – Lock, Block and Deadlock. So lets have a look on “Lock vs Block vs Deadlock”

These terms sound the same but are different in their meanings. Have a look on below definition to understand it.

Lock

When any session needs access to a piece of data from Database, a lock of some type is placed on that data to maintain the isolation property of database. Locking is different from blocking.

Block

When a session needs to wait for a resource being locked by another session, this process is called as blocking. e.g. If session “A” is waiting for a resource “R” being used by session “B”, you can say that session “B” is blocking session … More