SQL Server Locking, Blocking, Deadlocks and Concurrency
Quick Answer: SQL Server uses locks to protect data during concurrent transactions. Blocking happens when one transaction waits for another. A deadlock happens when transactions wait for each other in a cycle. Concurrency means handling multiple database operations safely.
1. Why Do We Need Locking and Concurrency?
Imagine Ravi works at a bank with 10,000 customer accounts. Two employees may update the same account at the same time.
Both employees need accurate account balances. Each transaction must finish without damaging another transaction's work.
How can SQL Server manage these operations safely?
The problem without proper concurrency control
Suppose Ravi sees an account balance of 50,000. Another employee also reads the same balance before either update finishes.
Both employees calculate new balances independently. Their updates may overwrite each other's changes.
This problem is called a lost update.
Example 1: Two sessions update the same bank account
Create this table first. Then follow the two session instructions using separate query windows.
CREATE TABLE BankAccount
(
AccountId INT PRIMARY KEY,
Balance DECIMAL(12, 2)
);
INSERT INTO BankAccount
VALUES (101, 50000);
Run Session A first, and stop before committing.
-- Session A
BEGIN TRANSACTION;
UPDATE BankAccount
SET Balance = 45000
WHERE AccountId = 101;
-- Keep this transaction open.
Now run Session B in another query window.
-- Session B
SELECT Balance
FROM BankAccount
WHERE AccountId = 101;
Under the default READ COMMITTED locking behavior, Session B waits for Session A's exclusive lock to be released.
This is blocking. It prevents Session B from reading an uncommitted update.
Now consider the problems in a system without proper concurrency control.
- Concurrent updates can overwrite earlier business changes.
- Transactions can wait longer than expected.
- Incorrect transaction design can create deadlocks.
- Long-running transactions can reduce application throughput.
Now think about thousands of banking transactions running simultaneously. We need safe access and predictable transaction behavior.
The solution: Locking, Blocking and Concurrency Control
Concurrency = multiple transactions working at the same time.
Lock = permission to access a database resource under specific rules.
Blocking = one transaction waits for an incompatible lock.
Deadlock = transactions wait for each other in a cycle.
SQL Server manages locks automatically during database operations. Developers control transaction boundaries and can improve query design.
Animation 1: How a SQL Server deadlock occurs
Watch Ravi's two bank transactions hold different account locks and wait for each other.
Each transaction holds one account lock and requests the other. SQL Server detects the cycle and rolls back a victim transaction.
2. Understand SQL Server Locks and Blocking
Lock modes = rules that control how transactions access database resources.
SQL Server uses different lock modes to protect reads and writes.
Shared Lock (S)
A Shared Lock protects data while a transaction reads it. Multiple transactions can hold compatible Shared Locks together.
Under locking READ COMMITTED, SQL Server normally releases read locks after the statement finishes.
Exclusive Lock (X)
An Exclusive Lock protects data during modifications. Other transactions cannot acquire incompatible locks on that resource.
SQL Server uses Exclusive Locks for operations such as UPDATE, INSERT and DELETE.
Update Lock (U)
An Update Lock helps SQL Server prepare for a data modification. It reduces certain lock conversion conflicts.
SQL Server also uses Intent, Schema and Bulk Update locks. The lock mode depends on the operation and execution plan.
What Is Blocking?
Blocking = a transaction waits because another transaction holds an incompatible lock.
In Ravi's bank system, Session A updates Account 101. Session B requests the same account before Session A commits.
Session B must wait until the conflicting lock is released. This protects the account from unsafe concurrent changes.
Important: Blocking is normal in a transactional database. Long waits and excessive blocking need investigation.
3. Understand Deadlocks and Concurrency
Deadlock = two or more transactions wait for resources held by each other.
Unlike normal blocking, a deadlock creates a circular waiting condition. None of the involved transactions can continue without intervention.
How does a deadlock happen?
Consider Ravi processing two account operations.
- Transaction A locks Account 101 first.
- Transaction B locks Account 202 first.
- Transaction A requests Account 202.
- Transaction B requests Account 101.
Each transaction waits for the other. SQL Server detects the deadlock and chooses a victim.
The victim transaction receives error 1205. SQL Server rolls back that transaction to release its locks.
Simulate a deadlock using two sessions
Example 2: Create two bank accounts for a deadlock test
Use a dedicated test database. Create the accounts before opening two query windows.
CREATE TABLE BankAccounts
(
AccountId INT PRIMARY KEY,
Balance DECIMAL(12, 2)
);
INSERT INTO BankAccounts
VALUES
(101, 50000),
(202, 30000);
Run Session A first. Keep its transaction open after the first update.
-- Session A: Execute first
BEGIN TRANSACTION;
UPDATE BankAccounts
SET Balance = Balance - 1000
WHERE AccountId = 101;
-- Wait while Session B locks Account 202.
UPDATE BankAccounts
SET Balance = Balance + 1000
WHERE AccountId = 202;
COMMIT TRANSACTION;
Now run Session B in another query window. Execute it after Session A's first update.
-- Session B
BEGIN TRANSACTION;
UPDATE BankAccounts
SET Balance = Balance - 500
WHERE AccountId = 202;
-- Session A must already hold Account 101.
UPDATE BankAccounts
SET Balance = Balance + 500
WHERE AccountId = 101;
COMMIT TRANSACTION;
Both transactions now hold different account locks. Each requests the account locked by the other.
Expected behaviour
Session A: Waiting for Account 202 Session B: Waiting for Account 101 SQL Server detects a deadlock. One transaction receives error 1205. The victim transaction is rolled back.
Testing warning: Run this only in a dedicated test database. Coordinate the two sessions carefully. SQL Server chooses the victim based on its deadlock resolution rules, not a guaranteed session order.
4. Quick Summary
Locking vs Blocking vs Deadlocks vs Concurrency
| Concept | Meaning |
|---|---|
| Locking | Protects database resources during transactions. |
| Blocking | One transaction waits for an incompatible lock. |
| Deadlock | Transactions wait for each other in a cycle. |
| Concurrency | Multiple transactions work safely at the same time. |
The important terms to remember
- Shared Lock: Allows compatible read access.
- Exclusive Lock: Protects modified data.
- Blocking: Waiting for another transaction's lock.
- Deadlock: Circular waiting between transactions.
- Deadlock Victim: Transaction selected for rollback.
5. Common Mistakes and Best Practices
1. Keeping transactions open for too long
Long transactions hold locks longer. Other transactions may wait unnecessarily.
Better approach: Keep transactions short and commit or roll back promptly.
2. Updating records in different orders
Different update orders can create circular waiting conditions.
Better approach: Access shared resources in a consistent order across transactions.
3. Treating every blocking event as a deadlock
Blocking is a normal wait. A deadlock requires a circular dependency.
Better approach: Check blocking chains and deadlock graphs before changing queries.
Design tip: Use suitable indexes, short transactions and consistent resource ordering. Consider row-versioning options when read workloads need less blocking.
6. How SQL Server Locking Comes Up in Interviews
Question 1: What is the difference between blocking and deadlocking?
What the interviewer expects: Explain the waiting conditions clearly.
Strong answer: "Blocking happens when one transaction waits for another transaction's lock. A deadlock happens when transactions wait for each other in a circular dependency. SQL Server resolves a deadlock by selecting a victim transaction."
Question 2: What is an Exclusive Lock?
What the interviewer expects: Explain write protection.
Strong answer: "An Exclusive Lock protects data being modified. Other transactions cannot acquire incompatible locks on that resource until the lock is released."
Question 3: How do you prevent deadlocks?
What the interviewer expects: Explain practical prevention methods.
Strong answer: "I keep transactions short and access resources in a consistent order. I also review execution plans, indexes and deadlock graphs. If a deadlock still occurs, I handle error 1205 with controlled retries."
7. Hands-On Practice
Beginner: Hospital appointments
Create a Patients table and an Appointments table. Open two sessions and update the same appointment record.
Expected result: Observe how one session waits for the other.
Intermediate: Bank account transactions
Simulate two transfers between different bank accounts. Run the updates in different orders.
Hint: Use explicit transactions and observe the blocking behaviour.
Advanced: Deadlock investigation
Use SQL Server Extended Events to capture a deadlock event.
- Inspect the deadlock XML graph.
- Identify the participating sessions.
- Find the resources each transaction holds and requests.
- Suggest a consistent resource access order.
Challenge: Fix the deadlock without removing transaction safety.
8. Next Steps and Community
Previous article: Query Execution Plans and SQL Server Performance Tuning
Next article: SQL Server Database Design – Build an Employee Management Database
For more details, read the official Microsoft documentation:
- Microsoft Learn: Transaction Locking and Row Versioning Guide
- Microsoft Learn: SQL Server Deadlocks Guide
Try the two-session bank account example and capture a deadlock graph. Share your findings and discuss your solutions with other learners.