MS SQL Server Optimization Question: Download Questions PDF
What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
Answer:
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.
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.
Download SQL Server Optimization Interview Questions And Answers
PDF
| Previous Question | Next Question |
| Explain Hostprotectionattribute in SQL server 2005? | What are the steps you will take, if you are tasked with securing an SQL Server? |