So you think you know SQL...Concurrency and Locking
The first in hopefully a fairly long series of posts on interview type questions/scenarios/etc., this one will cover Concurrency and Locking related Q/A (simple Q/A only at this point). The questions are separated into easy, moderate, and difficult groups. If you have any comments, by all means please post and lets discuss!
Typically, answers and content can be found in Books Online and/or MSDN...in this case, Craig Freedman's blog also contributed to the answers/scripts (https://blogs.msdn.com/craigfr/default.aspx).
For answers to these questions, go here when you're ready to see them: https://www.mssqltips.com/tip.asp?tip1253.
Additionally, see the attached script for some demo-ing of concurrency behaviors in different scenarios...
EASY:
Q: What isolation levels will provide completely read-consistent views of a database to all transactions?
Q: Within the READ_COMMITTED isolation level, during a read operation how long are locks held/retained for?
Q: Within the REPEATABLE_READ and SERIALIZABLE isolation levels, during a read operation and assuming row-level locking, how long are locks held/retained for?
Q: Can locks ever be de-escalated?
MODERATE:
Q: What are the different types of lock modes in Sql Server?
BONUS Q: Can you explain scenarios where each type of lock would be taken?
Q: What is lock escalation and what triggers it?
Q: Name as many of the lockable resources as possible in Sql Server 2005?
Q: What requirements must be met for a BULK-UPDATE lock to be granted, and what benefit do they serve?
Q: What is the least restrictive type of lock? What is the most restrictive?
Q: What is a deadlock and how is it different from a standard block situation?
Q: Which 2 isolation levels support optimistic/row-versioned-based concurrency control?
Q: What database options must be set to allow the use of optimistic models?
Q: What is the size of a lock structure?
DIFFICULT:
Q: Describe the differences between the pessimistic SERIALIZABLE model and the optimistic SNAPSHOT model in terms of transactional isolation (i.e., not the concurrency differences, but instead how the exact same transactional modifications may result in different final outcomes).
Q: In what circumstances will you see key-range locks, and what are they meant to protect against?
Q: Explain the purpose of INTENT locks
Q: Can deadlocks occur on resources other than database object?
BONUS Q: What different types of resources that can deadlock?
Q: Explain how the database engine manages the memory footprint for the lock pool when running in a dynamic lock management mode (different between Sql 2000 and Sql 2005)
Chad Boyd ~~~ This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.