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.
(e.g., process X is updating a row and has an exlusive lock on it and process Y wants to read that row... Y cannot put a shared lock on the row due to X's exclusive lock, so Y is "blocked" and must wait for X to finish).
DEADLOCK 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. So that they're waiting on each other (that is, they're "blocking" each other). Since neither can proceed until the other releases its lock on some resource, the SQL Engine will choose one of the processes to kill (known as a 'deadlock victim').
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.
(e.g., process X is updating a row and has an exlusive lock on it and process Y wants to read that row... Y cannot put a shared lock on the row due to X's exclusive lock, so Y is "blocked" and must wait for X to finish).
DEADLOCK 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. So that they're waiting on each other (that is, they're "blocking" each other). Since neither can proceed until the other releases its lock on some resource, the SQL Engine will choose one of the processes to kill (known as a 'deadlock victim').
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.
Nice Post...
ReplyDelete