Press "Enter" to skip to content

Category: Locks, Blocks, and Deadlocks

Capturing Deadlocks with the system_health Extended Event

Published 2021-04-19 by Kevin Feasel

Jack Vamvas is hunting deadlocks:

An application using SQL Server as the database backend was experiencing some application rollbacks. I decided to investigate the SQL Server to identify any errors which could be correlated to the application timeouts experienced by the users. 

I started reviewing the errors in the Extended Events system health logs, which are normally running by default on a SQL Server. They have a ton of useful information . I noticed a steady stream of deadlocks . This is the code used to create a permanent table to store the deadlock details , for review by the application team. 

Click through for the script.

Comments closed

Schema Modification Lock-Driven Deadlocks

Published 2021-04-13 by Kevin Feasel

Taryn Pratt diagnoses a deadlocking issue:

As expected, with the announcement that Stack Overflow for Teams is free for up to 50 users, we saw an incredible spike in sign-ups. Along with the spike in sign-ups, I started to receive a huge increase in alerts about deadlocks on the primary SQL Server for Teams. In the two weeks it took us to resolve the deadlocks, we hit at least 200 deadlocks.

Click through for the low-down on how they discovered and corrected the issue.

Comments closed

App Locks and Read Committed Snapshot Isolation

Published 2021-01-27 by Kevin Feasel

Michael J Swart has a tip for those who have RCSI turned on and are using app locks:

The procedure sp_getapplock is a system stored procedure that can be helpful when developing SQL for concurrency. It takes a lock on an imaginary resource and it can be used to avoid race conditions.

But I don’t use sp_getapplock a lot. I almost always depend on SQL Server’s normal locking of resources (like tables, indexes, rows etc…). But I might consider it for complicated situations (like managing sort order in a hierarchy using a table with many different indexes).

Click through to see how it normally works and how you should switch things up if you’re using Read Committed Snapshot Isolation.

Comments closed

A Primer on Locking and Deadlocks

Published 2020-12-10 by Kevin Feasel

John McCormack explains locks and deadlocks:

Blocking is the real world impact of locks being taken on resources and other lock types being requested which are incompatible with the existing lock. You need to have locks in order to have blocking. In the scenario where a row is being updated, the lock type of IX or X means that a simultaneous read operation will be blocked until the data modification lock has been released. Similarly, data being read blocks data from being modified. Again, there are exceptions to these based on the isolation level used.

Blocking then is a perfectly natural occurrence within SQL Server. In fact, it is vital to maintain ACID transactions. On a well optimised system, it can be hard to notice and doesn’t cause problems.

All things in moderation, even blocking.

Comments closed

Use a Separate Deadlock Extended Events Trace

Published 2020-12-04 by Kevin Feasel

Kendra Little explains why it makes sense to have an extended events trace specifically for deadlocks:

We recently had customer ask why SQL Monitor creates an Extended Events session to capture deadlock graphs, when SQL Server has a built-in system_health Extended Events trace which also captures deadlock information?

There are a couple of reasons why a dedicated trace is desirable for capturing deadlock graphs, whether you are rolling your own monitoring scripts or building a monitoring application. I like this question a lot because I feel it gets at an interesting tension/balance at the heart of monitoring itself.

Click through for the answer.

Comments closed

Preventing Deadlocks on Key Lookups

Published 2020-11-18 by Kevin Feasel

Erik Darling talks about key lookups:

I go back and forth when it comes to Lookups.

On the one hand, I don’t think the optimizer uses them enough. There are times when hinting a nonclustered index, or re-writing a query to get it to use a nonclustered index can really help performance.

On the other hand, they can really exacerbate parameter sniffing problems, and can even lead to read queries blocking write queries. And quite often, they lead to people creating very wide indexes to make sure particular queries are covered.

Read on for one scenario around deadlocking due to key lookups.

Comments closed

Detecting Spinlock Contention in SQL Server

Published 2020-10-29 by Kevin Feasel

Michael J. Swart walks us through spinlock contention:

When that occurred, the number of batch requests that the server could handle dropped significantly. So we saw more CPU use, but less work was getting done.

The high demand kept the CPU at 100% with no relief until the demand decreased. When that happened, the database seemed to recover. Throughput was restored and the database’s metrics became healthy again. During this trouble we looked at everything including the number of spins reported in the sys.dm_os_spinlock_stats dmv.

The spins and backoffs reported seemed extremely high, especially for the category “XVB_LIST”, but we didn’t really have a baseline to tell whether those numbers were problematic. Even after capturing the numbers and visualizing them we saw larger than linear increases as demand increased, but were those increases excessive?

Read on for the answer.

Comments closed

When Altering a Table Blocks the Log Reader

Published 2020-08-20 by Kevin Feasel

Aaron Bertrand walks us through a painful scenario:

We recently performed a DDL operation against a SQL Server table – simply increasing the size of a varchar column – which should have been instantaneous. Instead, we killed the SQL Server process after observing 20 minutes of HARD_SYNC_COMMIT waits and a blocked replication log reader. Could this issue have been avoided? What went wrong?

I spotted the issue pretty quickly, but it’s easy to miss in a code review. Read the whole thing.

Comments closed