Press "Enter" to skip to content

Category: Locks, Blocks, and Deadlocks

Reading a SQL Server XML Deadlock Report

Published 2025-01-09 by Kevin Feasel

Stephen Planck reads a report:

SQL Server includes an Extended Events session called system_health, which runs by default and, among other things, captures information about deadlocks as they occur. When two or more sessions block each other in such a way that no progress can be made (a deadlock), SQL Server chooses one session as the “victim,” rolls back its transaction, and frees resources so other sessions can continue. By reviewing the deadlock report in the system_health session’s XML output, you can see precisely why the deadlock happened and identify which queries or procedures were involved.

Below is a walkthrough of how to interpret a sample XML deadlock report, followed by a brief note on how to access this output.

Read on for that walkthrough.

Comments closed

The Benefits of Read Committed Snapshot Isolation

Published 2024-12-23 by Kevin Feasel

Haripriya Naidu gets optimistic:

In this article, we are going to take a look at how write operations block read operations under default isolation level – Read Committed and how to avoid this using the optimistic isolation level – Read Committed Snapshot.

I do wish that this were the default for new databases in SQL Server, like it is in Azure SQL Database.

H/T Brent Ozar’s newsletter for pointing me in Haripriya’s direction.

2 Comments

Waiting for Locks in Postgres

Published 2024-12-13 by Kevin Feasel

Hubert Lubaczewski wants to make a change:

I once wrote about this problem, but given that we now have DO blocks and procedures I can make nicer, easy to use example.

Over the years there have been many improvements to how long ALTER TABLE can take. You can now (in some cases) change datatype without rewrite or add default value.

Regardless how fast the thing works, it still needs extremely heavy (though shortlived) lock: Access Exclusive.

Read on to see how you can write a SQL operation that waits for a lock and, if it does not get this lock, retries with backoff.

Comments closed

Metadata-Only NOT NULL Column Insertion

Published 2024-11-20 by Kevin Feasel

Andy Brownsword has the need for speed:

When adding a new column and wanting to default the value for existing records, it used to be a painful task. As of SQL Server 2012 that became much easier.

But nobody told me, until Simon casually mentioned it in conversation recently. I had to see it for myself, so I thought I’d share for those who weren’t aware.

Read on to see how. I rarely self-promote in other people’s blog posts (hush, person who knows all the times I’ve done it), but I do have a talk on the topic of near-zero downtime database deployment strategies which includes this and quite a few other notes on what you can do without blocking others. For these sorts of changes, what you’re looking for is asynchronous processing and a Sch-M (schema modification) lock at the very end, such as when rebuilding an index with ONLINE = ON in Enterprise Edition. Alternatively, look for a Sch-M lock only on a metadata table and not the actual data. Andy’s post is an example of the latter.

Comments closed

Deadlock Resolution and Prevention in SQL Server

Published 2024-11-19 by Kevin Feasel

Eitan Blumin works through some deadlock issues:

Deadlocks in SQL Server can be frustrating and can cause significant performance and reliability issues. A deadlock occurs when two or more transactions are waiting for each other to release a lock on a resource, resulting in a situation where no transaction can proceed, and eventually, one of them is automatically killed and rolled back. This can happen when two transactions try to access the same data in a different order or when one transaction holds a lock on a resource while waiting for a lock held by another transaction. In this blog post, we’ll discuss how to troubleshoot and prevent deadlocks in SQL Server.

Click through for a way to get information on deadlocks, as well as three techniques for reducing the risk of deadlocks occurring.

Comments closed

Monitoring for Blocked Processes and Deadlocks with Extended Events

Published 2024-10-02 by Kevin Feasel

Lori Brown’s speaking my language:

Here is a way to set up an extended events session that can be used to collect blocked processes and deadlock records.  I use similar code to collect this type of data each day and have a job that pulls the data into tables which can be used to evaluate the resources that are being blocked and deadlocked on.

When checking for blocked process records, you must set the blocked process threshold in the configuration.  I have mine configured with the threshold set to 30 seconds.  This means that blocked process reports are generated every 30 seconds. 

Click through for that configuration setting, as well as the Extended Events session to do the work, and even code to pull the results into a table. It doesn’t get much simpler than that.

Comments closed

Service Broker Blocking on DisableQ

Published 2024-08-13 by Kevin Feasel

Tom Zika is angling to become the Service Broker Baron:

I was paged about a blocking chain where the blocked resource was a Service Broker queue, the lead blocked transaction was called CSbRollbackHandlerTask::DisableQ, the lock mode was SCH-M and even lock partitioning was involved. I won’t repro fully this time, but I’ve covered lock partitioning repro in my post Async-stats-update-causing-blocking. The goal was to capture an instance of transaction CSbRollbackHandlerTask::DisableQ with the same lock mode.

Click through for a demo and how to troubleshoot the issue.

Comments closed

SQL Server Compilation Time and Storage

Published 2024-08-12 by Kevin Feasel

Kendra Little explains how storage can affect query compilation time:

Up till now, I’ve thought of compilation time in SQL Server as being dependent only on CPU resources– not something that requires fast storage to be speedy. But that’s not quite right.

Slow storage can result in periodic long compile time in SQL Server. And long compile time not only extends the runtime for the query, it can also result in blocking with waits for compile locks.

Click through for more details, as well as a video by Erik Darling on compile-time locks.

Comments closed

Optimistic Locking in Postgres

Published 2024-07-30 by Kevin Feasel

Semab Tariq explains how optimistic locking works in PostgreSQL:

Concurrency control in databases ensures that multiple transactions can occur simultaneously without causing data errors. It’s essential because, without it, two people updating the same information at the same time could lead to incorrect or lost data. There are different ways to manage this, including optimistic locking and pessimistic locking. Optimistic locking assumes that conflicts are rare and only checks for them when updating data. In contrast, pessimistic locking assumes conflicts are likely and locks data early to prevent issues. Optimistic locking allows for more concurrent transactions and better performance in systems with fewer conflicts.

Read on to learn more about it, including some patterns for best use and what to avoid.

Comments closed

Blocking from Async Stats Updates

Published 2024-06-17 by Kevin Feasel

Tom Zika diagnoses an issue:

I recently encountered an issue where an index rebuild set to wait_at_low_priority ended up blocking an asynchronous statistics update. This interaction led to a large blocking chain where queries were waiting on the async stats update and started to timeout.

Read on for an explanation of all of the players involved, then a demo, and finally two solutions.

Comments closed