What is a deadlock and what is a live lock? How

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

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

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
Check out SET DEADLOCK_PRIORITY and "Minimizing Deadlocks" in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.

(Continued on next question...)

Other Job Interview Questions

• What are statistics, under what circumstances they go out of date, how do you update them?
• Explain CREATE DATABASE syntax
• What is the difference in Login Security Modes between v6.5 and 7.0?
• What is database replicaion? What are the different types of replication you can set up in SQL Server?
• What are the steps you will take to improve performance of a poor performing query?
• What’s the difference between a primary key and a unique key?
• What is blocking and how would you troubleshoot it?
• But what if you have to create a database with two filegroups, one on drive C and the other on drive D with log on drive E with an initial size of 600 MB and with a growth factor of 15%?
• What are statistics, under what circumstances they go out of date, how do you update them?
• What is database replicaion? What are the different types of replication you can set up in SQL Server?
• More...