Press "Enter" to skip to content

Category: Locks, Blocks, and Deadlocks

More Fun with Page Latches

Published 2025-09-03 by Kevin Feasel

Jared Poche continues a series on page latches:

In my previous blog, I set up a database with two tables, one with a large CHAR(8000) field and one with a smaller VARCHAR(100) field. Both tables use an INT IDENTITY column for their primary key. Since we’ll be inserting rows sequentially, we will see page latch contention when multiple threads attempt to insert.

We ran some initial tests with SQLQueryStress to create some page latch contention and resolved an odd problem causing connection delays.

We’ll use these two tables and test several different approaches to reduce page latch contention.

Jared shows the results for a variety of different tests and even has an embedded Excel spreadsheet, which is how you know he’s done his homework.

Leave a Comment

Error 845 Timeout in DBCC CHECKTABLE

Published 2025-09-02 by Kevin Feasel

Eitan Blumin troubleshoots an odd issue:

A customer reported that running DBCC CHECKTABLE on several different tables kept failing with the exact same error:

Msg 845, Sev 17: Time-out occurred while waiting for buffer latch type 4 for page (1:27527325), database ID 10.
Msg 1823, Sev 17: A database snapshot cannot be created because it failed to start.
Msg 7928, Sev 17: The database snapshot for online checks could not be created.

Read on to learn more about Eitan’s troubleshooting process, what the cause of the issue was, and the fixes (both the immediate and complete ones) needed to resolve the issue.

Leave a Comment

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.

Comments closed

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