MS SQL Server Optimization Interview Question:

What is a deadlock and what is a live lock? How will you go about resolving deadlocks?

Submitted by: Administrator

A deadlock can occur when two or more processes that strive to acquire resources are not able to acquire them due to some of the following reasons:

Two processes may be interdependent upon each other,

If there are more than 2 processes, then if a graph is plotted, then there could be a cycle,

A server maintains a graph. The processes acquiring the resources would intimate the server about their requirement. If the server detects an occurance of a deadlock, the user process is be terminated.

In a livelock, a request for an exclusive lock is denied repeatedly due to a series of overlapping shared locks that keep interfering.

An SQL Server detects this situation after four denials and then refuses further shared locks.

When a write transaction is forced to wait indefinitely due to read transactions that monopolize a table or a page then a live lock occurs.
Submitted by: Administrator