SQL Server and the Story of Locks — Does a Transaction Lock the Whole Table?

When I first learned SQL, I was just like you — barely passed the class, only remembering things like SELECT *, INNER JOIN, WHERE, and GROUP BY.

But when I started building real web applications, especially backend systems in .NET, I realized something important: SQL isn’t just a query language. It determines whether your system runs smoothly or gets stuck, whether your data stays accurate or turns into chaos.

And among all of its hidden mechanics, one of the most overlooked yet crucial features is Locking — the mechanism SQL Server uses to control how data is read and written simultaneously.

⚙️ 1. What Is a Lock?

A lock is basically a temporary “hold” that SQL Server places on data when you read, update, or delete it.

The goal is simple: prevent two people from changing the same row at the same time, which could cause inconsistent or incorrect data.

When multiple queries access the same data concurrently, SQL Server uses locks to:

1. 🔒 Keep data consistent.
2. 🔄 Ensure isolation between transactions — so they don’t step on each other.
3. 🚫 Avoid race conditions where two sessions overwrite each other’s changes.

📚 2. Main Types of Locks

SQL Server provides several types of locks depending on the operation being performed:

1. Shared (S) — used when reading data (SELECT).
2. Exclusive (X) — used when modifying data (INSERT, UPDATE, DELETE).
3. Update (U) — used to avoid deadlocks when reading before updating.
4. Intent locks (IS, IX) — indicate that lower-level locks exist on rows or pages.
5. Schema locks — used when changing the database structure (e.g., ALTER TABLE).

🧩 3. Lock Granularity

SQL Server can apply locks at different levels of granularity — meaning how much data is being locked at once:

1. 🔸 Row Lock — locks a single row.
2. 🔸 Page Lock — locks one page (~8 KB, usually containing several rows).
3. 🔸 Table Lock — locks the entire table.
4. 🔸 Database Lock — extremely rare, used when modifying the whole database.

SQL Server automatically decides which lock level to use:

1. When a query affects only a few rows → it uses ROWLOCK to minimize overhead.
2. When a query affects many rows → it may escalate to a PAGE LOCK or even a TABLE LOCK to reduce the cost of managing too many individual locks.

⚠️ If you ever notice that “the whole table seems to be locked” in your logs, it’s probably not a bug — it’s just lock escalation, where SQL Server automatically upgrades the lock level for efficiency.

🔄 4. Real-World Example — Why Locks Can Slow Things Down

Example 1: NOLOCK

Let’s start simple.

Suppose we create a table and insert two rows of data:

The situation: you need to fetch the UserBalance list, but thousands of users are running balance updates at the same time — countless sessions are hitting the table concurrently.

Meaning, you have to wait for all those transactions to finish and commit before your query can return any data — that’s when you start to see blocking and delays.

Giải pháp dùng with(nolock)

When one session is running an UPDATE, SQL Server places an Exclusive Lock (X) on that row. This means other sessions can only read it after the first transaction has committed.

The more transactions waiting on each other, the more your system starts to hang — leading to timeouts, queue build-ups, and overall slowness.

That’s why developers who don’t understand locking often end up writing code that slows down the entire system — even though their queries look “simple.”

Using WITH (NOLOCK) can make things super fast (and it’s used in most SELECT queries for that reason), but it comes with a big risk.

For example, imagine Session A updates a user’s balance to 0 but hasn’t committed yet. Another session reads that data with NOLOCK and thinks the user is broke, so it triggers a payment-error message.

A second later, Session A rolls back the transaction — the real balance is still 100 000.

But the other session has already raised a false error: “You’re out of money!”

Example 2: UPDLOCK

Now imagine two sessions both trying to deduct money at the same time.

Let’s say you only want to subtract funds if the user’s balance is ≥ 100 000 (and currently it is).

You first SELECT the balance to check the condition, then UPDATE the record if it passes:

When both sessions run at the same time, they both see the same balance — 100 000.

This means both satisfy the condition @money >= 100000 and both proceed to the UPDATE.

The expected result should be a remaining balance of 0, with one transaction failing due to insufficient funds.

But instead, both succeed, and the balance becomes -100 000 — clearly wrong.

👉 The fix: simply add WITH (UPDLOCK) in the SELECT statement.

This forces Session 2 to wait until Session 1 finishes before reading the balance.

That way, only one transaction can pass the condition and update at a time — no more double spending.

🔍 5. Common Hints to Control Locking

SQL Server lets you explicitly control how locking works using table hints — small directives you can add after a table name to tell the engine how to handle locks.

ví dụ:

Choosing the right hint depends on your use case:

1. Use NOLOCK for read-only reports where speed matters more than perfect accuracy.
2. Use UPDLOCK when reading before updating to avoid race conditions.
3. Use ROWLOCK when updating only a few rows and you want to reduce blocking.

🧠 6. Deadlock — When Two Sessions Wait Forever

A deadlock happens when two (or more) transactions each hold a lock on a resource, and both are waiting for the other to release it.

In other words: A is waiting for B, and B is waiting for A — forever.

➡️ Both transactions end up waiting for each other, so SQL Server has to terminate one to break the deadlock.

Understanding how UPDLOCK works helps you avoid this situation entirely.

💬 Conclusion

Locks in SQL Server are not bugs — they’re features.

The key is understanding how they work so you can:

1. Avoid unnecessary blocking.
2. Know when to use NOLOCK or UPDLOCK.
3. Write transactions that are safe, consistent, and high-performance.

Once you truly understand locking, your SQL code won’t just “work” — it’ll scale smoothly under real-world load.