SQL Server Concurrency and Locking Interview Questions

• Question 1: What is the size of a lock structure?
• Answer:  96 bytes
• Question 2: Can locks ever be de-escalated?
• Answer: No, locks are only escalated, never de-escalated. See http://msdn2.microsoft.com/en-us/library/ms184286.aspx.
• Question 3: What is the least restrictive type of lock? What is the most restrictive?
• Answer: The least restrictive type of lock is a shared lock. The most restrictive type of lock is a schema-modification
• Question 4: Within the REPEATABLE_READ and SERIALIZABLE isolation levels, during a read operation and assuming row-level locking, how long are locks held/retained for?
• Answer: Within either of these isolation levels, locks are held for the duration of the transaction, unlike within the READ_COMMITTED isolation level as noted above.
• Question 5: Name as many of the lockable resources as possible in SQL Server 2005?
• Answer:
• RID (single row on a heap)
• KEY (single row (or range) on an index)
• PAGE
• EXTENT
• HOBT (heap or b-tree)
• TABLE (entire table, all data and indexes)
• FILE
• APPLICATION
• METADATA
• ALLOCATION_UNIT
• DATABASE
• Question 6: Which 2 isolation levels support optimistic/row-versioned-based concurrency control?
• Answer: First is the the READ COMMITTED isolation level.  This is the only level that supports both a pessimistic (locking-based) and optimistic (version-based) concurrency control model. Second is SNAPSHOT isolation level that supports only an optimistic concurrency control model.
• Question 7: What database options must be set to allow the use of optimistic models?
• Answer: READ_COMMITTED_SNAPSHOT option for the read committed optimistic model. ALLOW_SNAPSHOT_ISOLATION option for the snapshot isolation level
• Question 8: Can deadlocks occur on resources other than database object?
• Answer: YES.
• Question 9: What is a deadlock and how is it different from a standard block situation?
• Answer: A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. In a deadlock situation, both transactions in the deadlock will wait forever unless the deadlock is broken by an external process – in a standard blocking scenario, the blocked task will simply wait until the blocking task releases the conflicting lock scenario.
• Question 10: Explain how the database engine manages the memory footprint for the lock pool when running in a dynamic lock management mode.
• Answer (SQL Server 2000): When the server is started with locks set to 0, the lock manager allocates two percent of the memory allocated to SQL Server to an initial pool of lock structures. As the pool of locks is exhausted, additional locks are allocated. The dynamic lock pool does not allocate more than 40 percent of the memory allocated to SQL Server.
• Generally, if more memory is required for locks than is available in current memory, and more server memory is available (the max server memory threshold has not been reached), SQL Server allocates memory dynamically to satisfy the request for locks. However, if allocating that memory would cause paging at the operating system level (for example, if another application was running on the same computer as an instance of SQL Server and using that memory), more lock space is not allocated.
• Answer (SQL Server 2005): When running in dynamic management mode (i.e. if the the server is started with locks configuration option set to 0), the lock manager acquires sufficient memory from the Database Engine for an initial pool of 2,500 lock structures. As the lock pool is exhausted, additional memory is acquired for the pool.
• Generally, if more memory is required for the lock pool than is available in the Database Engine memory pool, and more computer memory is available (the max server memory threshold has not been reached), the Database Engine allocates memory dynamically to satisfy the request for locks. However, if allocating that memory would cause paging at the operating system level (for example, if another application is running on the same computer as an instance of SQL Server and using that memory), more lock space is not allocated. The dynamic lock pool does not acquire more than 60 percent of the memory allocated to the Database Engine. After the lock pool has reached 60 percent of the memory acquired by an instance of the Database Engine, or no more memory is available on the computer, further requests for locks generate an error.
• Question 11: What are the different types of resources that can deadlock?
• Answer: Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system, and can occur for resources other than locks on database objects. Here are the resources:
• Locks - Waiting to acquire locks on resources, such as objects, pages, rows, metadata, and applications can cause deadlock.
• Worker threads - A queued task waiting for an available worker thread can cause deadlock. If the queued task owns resources that are blocking all worker threads, a deadlock will result
• Memory - When concurrent requests are waiting for memory grants that cannot be satisfied with the available memory, a deadlock can occur.
• Parallel query execution-related resources - Coordinator, producer, or consumer threads associated with an exchange port may block each other causing a deadlock usually when including at least one other process that is not a part of the parallel query. Also, when a parallel query starts execution, SQL Server determines the degree of parallelism, or the number of worker threads, based upon the current workload. If the system workload unexpectedly changes, for example, where new queries start running on the server or the system runs out of worker threads, then a deadlock could occur.
• Multiple Active Result Sets (MARS) resources - Resources used to control interleaving of multiple active requests under MARS, including:
• User resource - when a thread is waiting for a resource that is potentially controlled by a user application, the resource is considered to be an external or user resource and is treated like a lock
• Session mutex - The tasks running in one session are interleaved, meaning that only one task can run under the session at a given time. Before the task can run, it must have exclusive access to the session mutex.
• Transaction mutex - All tasks running in one transaction are interleaved, meaning that only one task can run under the transaction at a given time. Before the task can run, it must have exclusive access to the transaction mutex.
• Question 12: Within the READ_COMMITTED isolation level, during a read operation how long are locks held/retained for?
• Answer: When SQL Server executes a statement at the read committed isolation level, it acquires short lived share locks on a row by row basis. The duration of these share locks is just long enough to read and process each row; the server generally releases each lock before proceeding to the next row. Thus, if you run a simple select statement under read committed and check for locks, you will typically see at most a single row lock at a time. The sole purpose of these locks is to ensure that the statement only reads and returns committed data. The locks work because updates always acquire an exclusive lock which blocks any readers trying to acquire a share lock.
• Question 13: What are the different types of lock modes in SQL Server 2000 and 2005?
• Answer:
• Shared
• Update
• Exclusive
• Schema (modification and stability)
• Bulk Update
• Intent (shared, update, exclusive)
• Key Range (shared, insert, exclusive)
• Question 14: What isolation levels will provide completely read-consistent views of a database to all transactions?
• Answer (SQL Server 2000): Only the SERIALIZABLE isolation level will provide a completely read-consistent view of a database to a given transaction. In any of the other isolation levels, you could perceive some/all of the following, depending on the isolation level running in:
• Uncommitted dependency/dirty reads
• Inconsistent Analysis/non-repeatable reads
• Phantom reads (via insert/delete)
• Answer (SQL Server 2005): Both the SERIALIZABLE and SNAPSHOT isolation levels will provide a completely read-consistent view of a database to a given transaction. In any of the other isolation levels, you could perceive some/all of the following, depending on the isolation level running in:
• Uncommitted dependency/dirty reads
• Inconsistent Analysis/non-repeatable reads
• Phantom reads (via insert/delete)
• Question 15: What is lock escalation and what triggers it?
Answer: Lock escalation is when the system combines multiple locks into a higher level one. This is done to recover resources taken by the other finer granular locks. The system automatically does this. The threshold for this escalation is determined dynamically by the server.
Purpose:
·         To reduce system over head by recovering locks
·         Maximize the efficiency of queries
·         Helps to minimize the required memory to keep track of locks. 
• Question 16: Can you explain scenarios where each type of lock would be taken:
• Answer:
• SHARED - Used for read operations that do not change or update data, such as a SELECT statement.
• UPDATE - Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later. In a repeatable read or serializable transaction, the transaction reads data, acquiring a shared (S) lock on the resource (page or row), and then modifies the data, which requires lock conversion to an exclusive (X) lock. If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs. The second transaction attempts to acquire an exclusive (X) lock for its update. Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs. To avoid this potential deadlock problem, update (U) locks are used. Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock.
• EXCLUSIVE - Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
• INTENT - Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX). (Another question in the Difficult level section expands on this)
• SCHEMA - Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
• BULK UPDATE - Used when bulk copying data into a table and the TABLOCK hint is specified.
• KEY RANGE - Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.
• Question 17: Explain the purpose of INTENT locks?
• Answer: The Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level. Intent locks serve two purposes:
• To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.
• To improve the efficiency of the Database Engine in detecting lock conflicts at the higher level of granularity.
• Question 18: In what circumstances will you see key-range locks, and what are they meant to protect against?
• Answer: You will only see key-range locks when operating in the SERIALIZABLE isolation level.
• Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement. The serializable isolation level requires that any query executed during a transaction must obtain the same set of rows every time it is executed during the transaction. A key range lock protects this requirement by preventing other transactions from inserting new rows whose keys would fall in the range of keys read by the serializable transaction.
• Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions into a set of records accessed by a transaction.
• Question 19: What requirements must be met for a BULK-UPDATE lock to be granted, and what benefit do they serve?
• Answer: The Database Engine uses bulk update (BU) locks when bulk copying data into a table, and either the TABLOCK hint is specified or the table lock on bulk load table option is set using sp_tableoption. Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table while preventing other processes that are not bulk loading data from accessing the table.
• Question 20: 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).
• Answer: (See Craig Freedman’s Blog Post Here:http://blogs.msdn.com/craigfr/archive/2007/05/16/serializable-vs-snapshot-isolation-level.aspx)
• It is typically relatively simple to understand SERIALIZABLE. For the outcome of two transactions to be considered SERIALIZABLE, it must be possible to achieve this outcome by running one transaction at a time in some order.
• Snapshot does not guarantee this level of transactional isolation.
• Imagine the following sample scenario:
• There is a bag containing a mixture of white and black marbles. Suppose that we want to run two transactions. One transaction turns each of the white marbles into black marbles. The second transaction turns each of the black marbles into white marbles. If we run these transactions under SERIALIZABLE isolation, we must run them one at a time. The first transaction will leave a bag with marbles of only one color. After that, the second transaction will change all of these marbles to the other color. There are only two possible outcomes: a bag with only white marbles or a bag with only black marbles.
• If we run these transactions under snapshot isolation, there is a third outcome that is not possible under SERIALIZABLE isolation. Each transaction can simultaneously take a snapshot of the bag of marbles as it exists before we make any changes. Now one transaction finds the white marbles and turns them into black marbles. At the same time, the other transactions finds the black marbles - but only those marbles that where black when we took the snapshot - not those marbles that the first transaction changed to black - and turns them into white marbles. In the end, we still have a mixed bag of marbles with some white and some black. In fact, we have precisely switched each marble.
·         Question 21: Describe optimistic and pessimistic concurrency?
o    Answer: Optimistic concurrency: It assumes that resource conflicts between multiple users are very unlikely to occur and thus allows transactions to execute without any locking mechanisms on the resources. It is only while changing the data that a check is made on resources if any conflicts have occurred. If there’s a conflict then the application must read the data again and try to change it as well.
o    Pessimistic concurrency: Assumes that resource conflicts between multiple users are very likely to occur and hence locks resources as they are used by transactions for the duration of the transaction. A transaction is assured of successful completion unless a Deadlock ocurrs.

·         Question 22: What are the differences between lost updates and uncommitted dependencies?

o    Answer: Lost updates:  Last update overwrites other updates Cannot report on data that does not exist. Data is lost
o    Uncommitted dependencies: Access a row being updated by others. May report on data that does not exist Updates are lost

·         Question 23: What is a live lock?

Answer: When a request for exclusive lock is denied again and again because a series of overlapping shared locks are interfering with each other and to adapt from each other they keep on changing the status, it is known as live lock.

·         Question 24: What is a dead lock?

Answer: A dead lock occurs when 2 user processes have locks on 2 separate objects and each process is trying to acquire a lock on the object which is under the other process. SQL Server identifies dead locks and ends the dead lock by choosing one process and aborting the other process automatically. The aborted transaction is rolled back and an error message is sent to the user application. Usually, the transaction which needs the least amount of overhead to rollback is aborted.
·         Question 25: Describe in brief SQL Server locking.
Answer: SQL Server has 3 main lock types:
·         Shared: Locks are compatible with other shared and update locks.
·         Update: Locks are compatible with shared locks.
·         Exclusive: Locks are not compatible with any other locks.
Apart from lock types, there are transaction isolation levels for managing security among transactions:
·         READ UNCOMMITTED
·         READ COMMITTED
·         REPEATABLE READ
·         SERIALIZABLE
SQL Server has some locking optimizer hints along with lock types:
·         NOLOCK
·         HOLDLOCK
·         UPDLOCK
·         TABLOCK
·         PAGLOCK
·         TABLOCKX
·         READCOMMITTED
·         READUNCOMMITTED
·         REPEATABLEREAD
·         SERIALIZABLE
·         READPAST
·         ROWLOCK

·         Question 26: What guidelines should be followed to help minimize deadlocks? 

Answer: When two computer programs sharing the same resource prevent each other from accessing the resource results in a deadlock. This deadlock results in finishing the program.
Example:
P1 requests R1 and receives it. P2 requests R2 and receives it. P1 requests resource R2 and is queued up, pending the release of R2. P2 requests resource R1 and is queued up, pending the release of R1
Here, P and R is Program and Resource respectively.
Guidelines to minimize deadlocks:-
·         Avoid user interaction in the transactions. The transaction must not rely on any inputs from the user.
·         The concurrent transactions must access data in the same order. There should be consistency in which the operations occur
·         Transactions must be short and simple to avoid deadlocks. Long transactions may block other necessary activities
·         A lower isolation level like read committed must be used. Using lower isolation levels reduces the possibility of holding shared locks for a longer time
·         Bound connections should be used. Here, two or more connections opened by the same application can assist each other.