7 questions foundWhat is a lock in SQL Server, and why does the database engine use locks?
Beginner A lock is a mechanism SQL Server uses to control concurrent access to data, temporarily preventing other transactions from making conflicting changes to the same data at the same time, which protects the consistency and accuracy of your data when multiple users or processes are working with the database simultaneously.
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
-- This row is now locked until the transaction commits or rolls back
COMMIT TRANSACTION;
Real-world example A banking system locks an account row briefly during a balance update, preventing another transaction from reading or changing that same account's balance until the first update safely completes.
Common follow-ups: What is the difference between a shared lock and an exclusive lock?;How long does a lock typically stay in place during a transaction?
Transactions & ACID;Isolation & Locking
What are the four standard transaction isolation levels in SQL Server, and what does isolation level actually control?
Beginner The isolation level controls how much one transaction can be affected by changes made by other concurrently running transactions, with Read Uncommitted allowing the most interference, Read Committed being the default and more balanced, Repeatable Read providing more consistency by preventing changes to already read data, and Serializable providing the strictest, fully isolated behavior.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM Orders WHERE CustomerId = 1;
COMMIT TRANSACTION;
Real-world example A reporting application uses the default Read Committed isolation level for most queries, balancing reasonable consistency with good overall concurrency for the many simultaneous users accessing the system.
Common follow-ups: What is a dirty read, and which isolation level allows it to happen?;Which isolation level provides the strongest guarantees against interference from other transactions?
Transactions & ACID;Query Optimization & Plans
What is a deadlock, and how does SQL Server automatically handle one when it occurs?
Intermediate A deadlock happens when two or more transactions are each waiting for a lock held by the other, creating a cycle where neither can proceed, and SQL Server automatically detects this situation, choosing one transaction as the deadlock victim, rolling it back and returning an error, while allowing the other transaction to continue and complete successfully.
-- Transaction 1 locks OrderId 1, then waits for OrderId 2
-- Transaction 2 locks OrderId 2, then waits for OrderId 1
-- SQL Server detects this cycle and kills one transaction
Real-world example An order processing application encounters an occasional deadlock during a busy sales event, and after SQL Server automatically rolls back one of the conflicting transactions, the application simply retries it and the order completes successfully on the second attempt.
Common follow-ups: How do you write application code that properly handles and retries a deadlock error?;What tools help you investigate the actual cause of a deadlock after it happens?
Transactions & ACID;Error Handling with TRY CATCH
What is the difference between an optimistic and a pessimistic approach to handling concurrency, and where does SQL Server's snapshot isolation fit into this?
Intermediate A pessimistic approach assumes conflicts are likely and uses locks to prevent them from happening in the first place, while an optimistic approach assumes conflicts are rare and instead detects them after the fact, and snapshot isolation is SQL Server's optimistic option, letting readers see a consistent version of the data as it existed at the start of their transaction without blocking writers.
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT * FROM Orders;
COMMIT TRANSACTION;
Real-world example A reporting system uses snapshot isolation to run long running reports against a consistent view of the data, without blocking the many concurrent transactions that continue reading and writing to the same tables during that time.
Common follow-ups: What is the storage overhead of using snapshot isolation?;How does snapshot isolation differ from Read Committed Snapshot Isolation?
Transactions & ACID;In-Memory OLTP (Memory-Optimized Tables)
How would you diagnose and resolve a blocking problem where many queries are waiting on a single long running transaction?
Advanced You would query dynamic management views like sys.dm_exec_requests and sys.dm_tran_locks to identify the specific session holding the blocking locks, examine what that session is currently doing, and then either wait for it to complete, optimize the underlying query causing the delay, or in an emergency, safely terminate the blocking session if appropriate.
SELECT blocking_session_id, session_id, wait_type, wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
Real-world example A database administrator quickly identifies a poorly optimized report that had been holding locks for several minutes, blocking dozens of other users, and works with the reporting team to optimize the underlying query and prevent it from happening again.
Common follow-ups: How do you safely terminate a blocking session without causing data problems?;What tools provide a visual representation of blocking chains?
Query Optimization & Plans;SQL Server Profiler & Extended Events
How does Read Committed Snapshot Isolation differ from regular Read Committed isolation, and what tradeoff does enabling it introduce?
Advanced Read Committed Snapshot Isolation changes how the default Read Committed level behaves, using row versioning instead of shared locks for reads, which significantly reduces blocking between readers and writers, but introduces additional storage overhead in tempdb to maintain those row versions and a small amount of extra processing overhead for every data modification.
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;
Real-world example A high traffic e-commerce database enables Read Committed Snapshot Isolation, noticing significantly reduced blocking between customers browsing products and the background processes updating inventory counts, at the cost of a modest increase in tempdb usage.
Common follow-ups: How much additional load does this feature typically add to tempdb?;Does enabling this feature require any application code changes?
Transactions & ACID;Query Optimization & Plans
What are lock escalation and its potential impact on a busy system?
Intermediate Lock escalation happens when SQL Server converts many fine grained row or page level locks into a single, larger table level lock to reduce the memory overhead of tracking so many individual locks, but this can unexpectedly block other transactions that only needed access to a small, unrelated part of the same table.
-- Escalation typically occurs automatically
-- after a transaction acquires around 5000 locks
-- on a single table within one statement
Real-world example A large batch update unexpectedly escalates to a table level lock, briefly blocking unrelated queries trying to read other rows in the same table, prompting the team to break the batch update into smaller chunks to avoid triggering escalation.
Common follow-ups: How do you prevent lock escalation from happening during a large batch operation?;What are the signs that lock escalation is negatively affecting your system?
Query Optimization & Plans;Transactions & ACID