← Back to all posts
databasesql-serverpostgresqlconcurrencyarchitectureperformance

Locking in SQL Server and PostgreSQL

Locking in SQL Server and PostgreSQL

Reading Time: 5 minutes

Why Database Locking Matters

Application-level locking is only part of the story. Once multiple users or services hit the same database rows, concurrency shifts into the database engine itself.

Database locking affects:

  • correctness of updates
  • blocking behavior under load
  • transaction throughput
  • deadlocks and timeouts
  • how your application scales

If you work with SQL Server or PostgreSQL, understanding locking is essential for diagnosing production issues.

What a Lock Actually Does

A database lock coordinates access to data so concurrent transactions do not corrupt state.

Typical goals:

  • prevent lost updates
  • preserve transaction isolation
  • ensure consistent reads/writes

The tradeoff is that stronger guarantees can increase blocking and reduce concurrency.

Core Concepts

Shared vs exclusive access

At a high level:

  • shared/read locks allow reading while preventing conflicting writes
  • exclusive/write locks protect changes and block conflicting access

Blocking

Blocking happens when one transaction must wait for another transaction’s lock to be released.

Deadlocks

Deadlocks happen when two transactions wait on each other in a cycle. The database resolves this by killing one transaction.

SQL Server Locking Behavior

SQL Server is traditionally lock-oriented, though row-versioning isolation modes are also available.

Common lock granularity

SQL Server can lock at different levels:

  • row/key
  • page
  • table

The engine may escalate locks depending on workload.

Default isolation level

The default is typically READ COMMITTED.

In classic READ COMMITTED, readers can still be blocked by writers depending on configuration.

Read Committed Snapshot Isolation (RCSI)

Many SQL Server systems enable RCSI to reduce reader/writer blocking.

With RCSI:

  • reads use row versions from tempdb
  • readers do not block writers as often
  • writers still coordinate with each other

This is often one of the most impactful concurrency improvements in SQL Server workloads.

PostgreSQL Locking Behavior

PostgreSQL uses MVCC (Multi-Version Concurrency Control) heavily.

That means readers usually do not block writers in the same way traditional lock-based systems do.

Practical result

  • reads often see a consistent snapshot
  • writers still contend with other writers
  • row-level locking is still very important for updates and SELECT ... FOR UPDATE

PostgreSQL’s model often feels more concurrency-friendly by default, but it is not lock-free.

Isolation Levels Matter More Than Most Teams Think

The same code can behave very differently depending on isolation level.

Common levels:

  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

Stronger isolation improves correctness guarantees, but can increase retries, blocking, or transaction failures depending on the engine.

Practical rule

Do not raise isolation level casually. Treat it as a correctness/performance tradeoff.

Typical Write Contention Scenario

Imagine two transactions updating the same order row.

Both SQL Server and PostgreSQL must coordinate that update.

Likely outcomes:

  • one transaction waits
  • one transaction retries
  • one transaction deadlocks or gets a serialization error depending on isolation and execution path

The exact behavior differs, but the design lesson is the same: hot rows become concurrency bottlenecks.

SELECT ... FOR UPDATE and Explicit Row Locking

PostgreSQL commonly uses:

SELECT *
FROM accounts
WHERE id = 42
FOR UPDATE;

This explicitly locks selected rows for update.

SQL Server often uses hints or update patterns to achieve similar intent, for example:

SELECT *
FROM Accounts WITH (UPDLOCK, ROWLOCK)
WHERE Id = 42;

These tools can be useful, but they should be used deliberately. Lock hints are not a substitute for sound transaction design.

Common Production Problems

1. Long transactions

Long-running transactions hold locks longer and increase blocking risk.

Examples:

  • opening transaction too early
  • external API calls inside DB transaction
  • large batch updates without chunking

2. Hot rows

If every request updates the same row, throughput collapses quickly.

Examples:

  • central sequence tables
  • single global counters
  • one shared settings row

3. Missing indexes

Without proper indexes, the database may scan more rows/pages than necessary, increasing lock scope and contention.

4. Inconsistent access order

When different code paths update tables in different order, deadlock probability increases.

SQL Server vs PostgreSQL: Practical Comparison

SQL Server

Strengths:

  • mature locking/transaction tooling
  • excellent operational visibility for blocking/deadlocks
  • strong support for tuning lock behavior and row-versioning modes

Watch for:

  • reader/writer blocking if row-versioning is not enabled
  • lock escalation in heavy workloads
  • hot-page or hot-table contention

PostgreSQL

Strengths:

  • strong MVCC model
  • readers typically avoid blocking writers more often by default
  • good row-level concurrency behavior

Watch for:

  • write/write contention still exists
  • long-running transactions can retain old row versions and cause bloat
  • serialization and retry logic matter in stronger isolation modes

Designing to Reduce Locking Problems

Good concurrency behavior usually comes from design more than clever SQL.

Useful strategies:

  • keep transactions short
  • avoid external calls inside transactions
  • update rows in consistent order
  • add proper indexes
  • reduce hot-row patterns
  • use batching for large writes
  • prefer append-only/event models when appropriate

Handling Deadlocks and Retries

Deadlocks are normal in sufficiently concurrent systems. They are not automatically a sign of broken software.

The right response is usually:

  • log deadlock context
  • keep transactions short
  • standardize table access order
  • add retry logic for transient failures

Both SQL Server and PostgreSQL workloads often benefit from controlled retry policies for transient concurrency failures.

ORM Developers Still Need to Know This

If you use EF Core, Dapper, or another data access library, locking still matters.

The ORM does not eliminate:

  • long transactions
  • poor isolation choices
  • hot rows
  • missing indexes
  • deadlocks

A lot of “EF Core performance issues” are really concurrency and transaction design issues at the database layer.

Summary

Locking in SQL Server and PostgreSQL is about balancing correctness with concurrency.

Key ideas:

  • SQL Server is more visibly lock-centric, though row-versioning options help a lot
  • PostgreSQL relies heavily on MVCC, but writers still contend
  • long transactions, hot rows, and missing indexes are major sources of pain
  • isolation level and transaction design matter more than most teams realize

If your application experiences blocking, timeouts, or deadlocks under load, the solution is usually not “avoid locking entirely.” The solution is to understand the engine, shorten the critical path, and design for concurrency intentionally.

References