All 7 Isolation & Locking questions
More in Isolation & Locking
What are the four standard transaction isolation levels in SQL Server, and what does isolation level actually control? What is a deadlock, and how does SQL Server automatically handle one when it occurs? 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? What are lock escalation and its potential impact on a busy system? How would you diagnose and resolve a blocking problem where many queries are waiting on a single long running transaction? How does Read Committed Snapshot Isolation differ from regular Read Committed isolation, and what tradeoff does enabling it introduce?
Advertisement
SQL Server › Isolation & Locking

What is a lock in SQL Server, and why does the database engine use locks?

Beginner

Answer

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
All 7 Isolation & Locking questions