LATEST 🚀 Welcome to Upgradix by Shrash Studio – Your destination for free technology learning and career growth.
🕘 Updated:
MS SQL Server

SQL Server Locking, Blocking, Deadlocks and Concurrency

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.

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.

  1. Concurrent updates can overwrite earlier business changes.
  2. Transactions can wait longer than expected.
  3. Incorrect transaction design can create deadlocks.
  4. 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.

Ravi Bank operations clerk
starts two transactions
Transaction A Locks Account 101
Transaction B Locks Account 202
requests the other account
Transaction A Waits for Account 202
Transaction B Waits for Account 101
forms a waiting cycle
Deadlock Detected SQL Server selects a victim
Transaction starts Waiting transaction Deadlock resolution Current animation stage

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.

Shared Lock (S) Used for compatible read access
Exclusive Lock (X) Protects modified data
Update Lock (U) Used before certain updates
Intent Locks Show lower-level lock intentions

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

Important SQL Server transaction concepts
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:

Try the two-session bank account example and capture a deadlock graph. Share your findings and discuss your solutions with other learners.

Comments & Ratings (0)

Log in or create a student account to comment and rate this article.

Be the first to comment on this article.