Press "Enter" to skip to content

Category: Locks, Blocks, and Deadlocks

Building a Test Bed for Page Latch Waits

Published 2025-07-28 by Kevin Feasel

Jared Poche generates some test data:

I’ve already posted a blog on page latch waits and some of the ways to minimize them, but I wanted to add some more on the issue. I wanted to test out some strategies to see how effective they are.

Setting up tests can be very difficult in some cases, and this attempt encountered a very unusual problem. At the risk of delaying the intended results, I wanted to point this out. It’s both interesting and frustrating to work with SQL Server for 20 years and run into new problems.

Read on to see what Jared tried, some interesting consequences, and a small fix that enabled a big problem.

Leave a Comment

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.

Comments closed

Capturing Blocking via Extended Event

Published 2025-07-09 by Kevin Feasel

Tom Collins has a pair of scripts:

Step 1 : Create the SQL Server  Extended Event and output to and event file

Step2 : Excecute this query to read the output of the xel files generated by the Extended Events Session .   

There’s not a lot of commentary around these, but the session includes blocked processes and deadlocks, so that’s nice if you have intermittent blocking chain issues.

Comments closed

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