SQL Server: Brief explanation
on Locking, Blocking, Deadlock
Like ever, today’s article of Pinal Dave was interesting and
informative. After, our mutual discussion between our DBAs and Developers on
Pinal Dave topic of Concurrency and Isolation. I felt that most of us are
intermingling three equally sounding words. Those are LOCKING, BLOCKING and
DEAD LOCKING.
Lets try to revisit these concepts with some simple analogies.
LOCKING occurs when connection needs access to a piece of data
in database and it’s necessary for SQL Server when managing multiple
connections. Just assume an example of your garage, when you park your car in
garage, basically you are locking the place of garage.
BLOCKING occurs when two connections need access to same piece
of data concurrently and one connection is blocked because at a particular
time, only one connection can have access. Just like, you stop (block) your car
on a traffic signal because some other car or cars are using the crossing area.
DEAD LOCK occurs when one connection is blocked and waiting for
a second to complete his work, but on other side, second connection is also
waiting for first connection to release the lock. Just like, you need to cross
the signal area but same time someone else from opposite side also want to
cross the signal. Now, you need a way which other is holding and other need way
where your car is.
That is why, one should be clear that locking is integral part
of SQL Server to handle concurrency, blocking is bad when one
connection/transaction is waiting unnecessary for a long time, and deadlocking
is a phenomenon which should never occur.
No comments:
Post a Comment