SQL Server DBA Concurrency and Locking Interview Questions

By: Chad Boyd   |   Comments (3)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > Professional Development Interview Questions DBA

Problem

In my environment, we have frequent locking and blocking.  As we bring on additional team members, I need to interview DBAs and Developers to get a sense of their SQL Server concurrency and locking knowledge.  With some of the changes from SQL Server 2000 and 2005 we have some new opportunities.  As such, how can I gauge their knowledge on these topics?

Solution

Check out the following SQL Server Concurrency and Locking interview questions:

Question Difficulty = Easy

• Question 1: 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 2: 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 3: 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 4: Can locks ever be de-escalated?
  • Answer: No, locks are only escalated, never de-escalated.

Question Difficulty = Moderate

• Question 1: 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 2: 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 3: What is lock escalation and what triggers it?
  • Answer: The process of converting many fine-grained locks into fewer coarse-grained locks.
    • Escalation reduces system resource consumption/overhead while increasing the possibility of concurrency conflicts
    • To escalate locks, the Database Engine attempts to change the intent lock on the table to the corresponding full lock, for example, changing an intent exclusive (IX) lock to an exclusive (X) lock, or an intent shared (IS) lock to a shared (S) lock). If the lock escalation attempt succeeds and the full table lock is acquired, then all heap or B-tree, page (PAGE), key-range (KEY), or row-level (RID) locks held by the transaction on the heap or index are released. If the full lock cannot be acquired, no lock escalation happens at that time and the Database Engine will continue to acquire row, key, or page locks.
    • Lock escalation is triggered at either of these times:
      • When a single Transact-SQL statement acquires at least 5,000 locks on a single table or index.
      • When the number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.
      • If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired.
• Question 4: 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 5: 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 6: 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 7: 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 8: 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 9: 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 10: What is the size of a lock structure?
  • Answer:  96 bytes

Question Difficulty = Difficult

• Question 1: 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 2: 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 3: Can deadlocks occur on resources other than database object?
  • Answer: YES.
• Question 4: 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 5: 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 6: 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: 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.

Next Steps

• Check out this script which serves as a good point of reference for these questions and answers.  You can walk through a number of examples that will thoroughly explain these topics.
• The next time you need to prepare for an interview or conduct one, consider this tip for the interview process.
• Check out the following related tips:
  • Minimally Logging Bulk Load Inserts into SQL Server
  • Deadlock Priority Configuration
  • DBCC SHOWCONTIG Blocking Issues
  • Finding and troubleshooting SQL Server deadlocks
  • How To: Graphical Deadlock Chain
  • Capturing Deadlock Information in XML Format

About the author

Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips