Press "Enter" to skip to content

Category: Locks, Blocks, and Deadlocks

Event Notification via LISTEN/NOTIFY in PostgreSQL Doesn’t Scale

Published 2025-07-11 by Kevin Feasel

Elliot Levin takes us through a performance issue:

We love Postgres and it lives at the heart of our service! But this extremely concurrent, write-heavy workload resulted in a stalled-out Postgres. This is the story of what happened, how we ended up discovering a bottleneck in the LISTEN/NOTIFY feature of Postgres (the event notifier that runs based on triggers when something changes in a row), and what we ended up doing about it.

Click through for details, as well as what the team there did to migrate away from this feature.

Leave a Comment

Transaction Control and Locking in PostgreSQL vs Oracle

Published 2025-06-23 by Kevin Feasel

Kellyn Gorman continues a series on PostgreSQL for Oracle DBAs:

Now that we finished Part 2 on physical data structures, storage and processes, it’s time to work our way into transaction control, locking and just a smidgen of performance insight.

This post is for Oracle DBAs who want to understand PostgreSQL’s transaction and locking mechanics, as well as how to monitor and tune performance without diving into execution plans just yet. Think of this as your quick-reference mental shift guide from Oracle to PostgreSQL.

Read on for some of the differences in the platforms.

Comments closed

The Downsides of SELECT FOR UPDATE in PostgreSQL

Published 2025-06-17 by Kevin Feasel

Laurenz Albe explains why SELECT FOR UPDATE is rarely the right call:

Recently, while investigating a deadlock for a customer, I was again reminded how harmful SELECT FOR UPDATE can be for database concurrency. This is nothing new, but I find that many people don’t know about the PostgreSQL row lock modes. So here I’ll write up a detailed explanation to let you know when to avoid SELECT FOR UPDATE.

Click through for the full explanation.

Comments closed

Last Page Insert Contention

Published 2025-05-15 by Kevin Feasel

Haripriya Naidu is trying to slam a lot of transactions through the same door:

When operations wait to acquire a latch on a page, you’ll see a wait type called PAGELATCH. A latch is a lightweight lock. PAGELATCH waits typically occur in TempDB on pages like PFS, GAM, SGAM, or system object pages.

Normally, you won’t see PAGELATCH waits in user databases because user objects don’t usually experience the same level of concurrent inserts/updates/deletes as temp tables do.

But, there is one case where this can happen:
When many concurrent transactions try to insert into the last page of a table, they all compete for a latch on that page. This results in last page insert contention.

Read on to see when this happens, as well as a demonstration of it. Haripriya then uses a bit of functionality that is available in recent versions of SQL Server to resolve the issue.

Comments closed

Darling Data Blocking Monitor App

Published 2025-05-05 by Kevin Feasel

Erik Darling announces a tool.

If you’ve given Erik money in the past (which I can confirm is an easy enough task). If you’ve used sp_WhoIsActive to write to a table, you’ve got an idea of how it will work. But this looks quite a bit easier than setting it all up yourself.

And the next time you run into Josh Darnell, say thank you.

Comments closed

Monitoring PostgreSQL Activity

Published 2025-04-09 by Kevin Feasel

Andrea Gnemmi looks at a pair of extensions:

We all know the importance of monitoring our RDBMS to ensure the performance and availability. Are there any tools that provide functionality to better to monitor PostgreSQL databases? In this article we will explore pg_stat_activity and pg_locks.

Click through for an overview of these two extensions and some of the functionality available in them.

Comments closed

SELECT FOR UPDATE in PostgreSQL

Published 2025-04-09 by Kevin Feasel

Umair Shahid preps for an update:

When multiple transactions need to modify the same rows, ensuring data consistency can become tricky. A single wrong approach to locking can lead to suboptimal performance or even bring your application to a standstill as numerous transactions block one another. One tool in PostgreSQL’s arsenal to handle concurrency is SELECT FOR UPDATE. It allows you to lock specific rows before updating them, preventing other transactions from modifying those rows until your transaction completes.

In this blog, we will dive deep into SELECT FOR UPDATE in PostgreSQL. We will explore how it helps in reducing contention, avoiding deadlocks, and ultimately boosting performance when dealing with highly concurrent applications.

Click through to understand how this works and also some notes on when to use it and when not to use it.

Comments closed

The Implications of TABLOCK in SQL Server

Published 2025-02-20 by Kevin Feasel

Haripriya Naidu locks the table:

TABLOCK can greatly improve insert performance under the right conditions, but it has some implications to consider. It’s important to understand where this hint cannot be used to avoid shooting yourself in the foot, specifically due to the locking behavior of TABLOCK.

In this article, we’ll explore how TABLOCK’s locking behavior makes it less suitable for tables that cannot afford significant blocking.

Click through for a demo and some takeaways.

Comments closed

Lock Escalation in SQL Server

Published 2025-02-20 by Kevin Feasel

Stephen Planck talks lock escalation:

Lock escalation is a built-in mechanism in SQL Server designed to optimize how the database engine manages locks. By reducing thousands of fine-grained locks (such as row or page locks) to a single table-level lock, SQL Server aims to lower overhead in the lock manager. In practice, lock escalation saves system resources but can also introduce challenges in high-concurrency environments. This post explores why lock escalation happens, how it works under the hood, the problems it can cause, and strategies to prevent or mitigate unwanted escalations.

Read on for the full story.

Comments closed