Locks, Blocks, and Deadlocks

Five Locking Behaviors to Watch for in PostgreSQL

Published 2026-05-28 by Kevin Feasel

PostgreSQL uses MVCC (Multi-Version Concurrency Control) for concurrency control: reads never block writes, and writes never block reads.

Its locking system has 8 table-level lock modes and 4 row-level lock modes, and the conflict tables in the documentation tell you exactly which lock modes conflict with which.

In practice, though, once you actually operate PostgreSQL, locks end up conflicting in places you never expected. Queries take far longer than anticipated, and in the worst case you end up with an outage.

Click through for five notes around locking that may not be immediately apparent.

Adding Foreign Keys and Deadlocks

Published 2026-05-21 by Kevin Feasel

Michael J. Swart explains a challenge in adding a foreign key to an existing table:

Schema modification locks (SCH-M) are taken by DDL (Data Definition Language) statements like CREATE/ALTER/DROP.
Schema stability locks (SCH-S) are taken by DML (Data Manipulation Language) statements like INSERT/UPDATE/DELETE.

Those two types of locks are incompatible. Meaning, I can’t get a SCH-S lock on some table if you’ve already got a SCH-M lock on it (and vice versa).
Paul Randal describes the SCH-M lock as a super-table-X lock. It makes sense to me, if I’m half way through querying a table, I don’t want its definition to change.

Such a pessimistic lock can be awkward for a busy system. The SCH-M can cause a lot of blocking. For example, creating (and dropping) foreign keys requires a SCH-M lock not only on the parent table, but also on the referenced table which leads to trouble.

Click through for a demonstration of the problem. Michael also has some guidance on how to minimize the issue. I’d note the degenerative form of this guidance: understand your data model up-front and apply foreign key constraints at table creation time. That’s not always possible, sure, so when you can’t do that, Michael has some good advice.

Optimized Locking in SQL Server 2025

Published 2026-05-11 by Kevin Feasel

John Deardurff enables a feature:

Modern SQL Server workloads demand high concurrency without sacrificing consistency. Traditionally, we relied on locking to enforce correctness, but that came with blocking and performance challenges. To resolve some of these issues, row versioning was used to reduce some of that contention overhead. But now with optimized locking in SQL Server 2025, we have a more efficient and scalable concurrency model.

Click through for a demo. My point of curiosity is, what’s the impact in a practical but busy environment? I don’t have one of those running SQL Server 2025, so I do wonder when we’ll get the tell-all post from someone in a very busy environment who has it on. (Or, if we already have and I’ve missed it, please do let me know!)

2 Comments

Reviewing Blocking and Deadlocking

Published 2026-04-10 by Kevin Feasel

Erik Darling digs into more functionality in his performance monitoring tool:

In this video, I delve into the blocking and deadlock monitoring capabilities of FreeSQL Server Performance Monitoring, a tool I’ve developed and made available on GitHub. With a focus on practicality and ease-of-use, I explain how we leverage extended events for both blocking and deadlock scenarios, ensuring that you can identify and address performance issues efficiently. Whether you’re using system health or prefer to rely on the block process report, my monitoring tool streamlines the process by automatically setting up necessary configurations and providing easy-to-understand visualizations of your SQL Server’s performance trends over time.

Click through for the video and transcript.

Comments closed

Lock after Qualification in SQL Server 2025

Published 2026-03-02 by Kevin Feasel

Hugo Kornelis has a new video:

One of these two features is Lock After Qualification (LAQ). This feature avoids the scenario where a delete or update is blocked by a locked row that would not qualify. But, like any good thing, there is a price. This video shows the feature, explains how it works, and shows some of the potentially undesired side effects.

Click through for the video.

Comments closed

Locks in Microsoft Fabric Data Warehouse

Published 2025-10-09 by Kevin Feasel

Twinkle Cyril enumerates the lock types in Fabric Data Warehouse:

Fabric DW supports ACID-compliant transactions using standard T-SQL (BEGIN TRANSACTION, COMMIT, ROLLBACK) and enforces snapshot isolation across all operations. Locks in Fabric Data Warehouse are used to manage concurrent access to metadata and data, especially during DDL operations. Here’s how locking works:

Click through for a chart. The locking policy is a lot simpler than what we see in SQL Server and you can see a description of the pros and cons of that simpler approach.

Comments closed

Bitmap Indexes and Deadlocks in Oracle

Published 2025-09-24 by Kevin Feasel

David Fitzjarrell looks at bitmap indexes:

Bitmap indexes can be very useful, especially when NULL columns are present, as a bitmap index will include such values when btree indexes may not, such as entirely null index keys. Unfortunately bitmap indexes do not behave well with concurrent transactions, where deadlocks may arise because of the bitmap index.

Oracle will trap, report and “resolve” deadlocks by assessing the situation, determining which session created the deadlock and killing the ‘offending’ session, with no manual intervention required. The trace file generated reports this as an issue with application coding and/or logic and in many cases this is the likely cause. Enter the bitmap index and a concurrent transaction and, mysteriously, a deadlock may appear, confounding the developer and the DBA.

Read on to learn more about how bitmap indexes can provide a (potentially) strange source of deadlocks.

Comments closed

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.

Comments closed

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.

Comments closed

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

Category: Locks, Blocks, and Deadlocks