PostgreSQL vs MSSQL Locking: Architectural Differences and Practical Patterns

In my previous article, I detailed lock hints in MSSQL. But what are the differences when moving to PostgreSQL? Does „Postgres” have its own equivalents for NOLOCK or UPDLOCK? I will try to answer that by analyzing the architecture of both systems and the most popular concurrency patterns.

Lock Manager vs. MVCC

Before we dive into the syntax, we must understand a fundamental difference. MSSQL (by default) relies on a Lock Manager—a system that tracks locks in RAM. In MSSQL, „Readers can block Writers,” and lock escalation (moving from row-level to table-level locks) is a constant performance risk.

PostgreSQL is built on MVCC (Multi-Version Concurrency Control). Here, every change creates a new version of a row. Readers never block Writers, and Writers never block Readers. However, in critical moments—like financial transactions or task queues—we must manually take control. We do this using the SELECT ... FOR family of clauses.

1. The „Check-Then-Update” Pattern (Preventing Lost Updates)

MSSQL: WITH (UPDLOCK) | PostgreSQL: FOR UPDATE

Scenario: You fetch a balance, modify it in your application, and save it back. You want to ensure that no one else changed that same row while you were performing your calculations.

SQL

BEGIN;

SELECT balance 
FROM accounts 
WHERE id = 1 
FOR UPDATE;

-- Application logic

UPDATE accounts 
SET balance = 900 
WHERE id = 1;

COMMIT;

Why it works: FOR UPDATE acts as an exclusive row-level lock. Any other transaction trying to UPDATE that specific ID will be queued until you commit your changes. This is the primary defense against Lost Updates.

2. The „Queue Processor” Pattern (High Throughput)

MSSQL: WITH (UPDLOCK, READPAST) | PostgreSQL: FOR UPDATE SKIP LOCKED

Scenario: You have a job table and multiple background workers. You want each worker to grab the first available task without waiting for others or causing collisions.

SQL

BEGIN;

SELECT * FROM job_queue 
WHERE status = 'pending' 
ORDER BY created_at 
LIMIT 1 
FOR UPDATE SKIP LOCKED;

UPDATE job_queue SET status = 'processing' WHERE id = :selected_id;

COMMIT;

Why it works: SKIP LOCKED tells Postgres to ignore rows that are already locked by other transactions. This allows multiple workers to process the same table simultaneously with zero wait time, maximizing horizontal scalability.

3. The „Upsert Guard” (Manual Uniqueness)

MSSQL: WITH (UPDLOCK, HOLDLOCK) | PostgreSQL: INSERT ... ON CONFLICT

In MSSQL, you often need heavy locks to check if a record exists before inserting. Postgres solves this with a native, atomic syntax.

SQL

INSERT INTO users (email) 
VALUES ('dev@example.com') 
ON CONFLICT (email) DO NOTHING;

Why it works: Postgres handles this at the index engine level. It is much more efficient and safer than manually checking IF NOT EXISTS with manual locks, which often leads to deadlocks in MSSQL.

4. The „Fail Fast” Strategy (Low-Latency APIs)

MSSQL: WITH (NOWAIT) | PostgreSQL: FOR UPDATE NOWAIT

Scenario: Your API must respond quickly. If a record is locked by a long-running report, you don’t want your web thread hanging for 30 seconds waiting for the lock to release.

SQL

BEGIN;

SELECT * FROM orders 
WHERE id = 10 
FOR UPDATE NOWAIT;

COMMIT;

Why it works: Instead of causing Thread Starvation in your application pool, your app immediately knows the record is busy and can return a „Try again later” message or a 409 Conflict status.

5. The „Dirty Read” Dashboard (Performance First)

MSSQL: WITH (NOLOCK) | PostgreSQL: Not Applicable (Default Behavior)

The most common question from MSSQL devs is: „Where is NOLOCK in Postgres?”. The short answer: You don’t need it.

SQL

SELECT count(*) FROM big_table;

Why it works: Because of MVCC, Postgres always serves a consistent snapshot. You don’t risk „Dirty Reads” (reading uncommitted data as you do with MSSQL’s NOLOCK), yet you still get high performance without blocking anyone.

Summary: Mapping Your Knowledge

Bibliography

PostgreSQL „Chapter 13. Concurrency Control”. Official Docs
Cookbooks CarlFurrow – For update skip locked
Haril – Select for update in postgresql