Press "Enter" to skip to content

Category: Locks, Blocks, and Deadlocks

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

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