Press "Enter" to skip to content

Category: Locks, Blocks, and Deadlocks

Analysis Services Locking And Blocking

Bill Anton shows how to figure out if your Analysis Services performance problem is due to locks:

This past weekend, after one of my presentations on Analysis Services at SQL Saturday 520, an attendee described a performance issue in his environment that he thought could be related to locking and blocking and asked if there was a way to confirm if this was the cause. The short answer is yes, absolutely!

While I wouldn’t say this is a common issue across *all* analysis services environments, it’s definitely common amongst the subset of analysis services environments that do intra-day processing – which is when the SSAS database is being processed during hours of the day when users might be simultaneously querying it.

This is a very interesting article.  I’d never thought about SSAS taking locks, but it makes perfect sense.

Comments closed

WideWorldImporters Deadlocks

Kendra Little has a couple queries to force deadlocks in the WideWorldImporters database:

SQL Server’s deadlock manager woke up, looked around, and saw that our two session windows were stuck. They each were requesting locks that the other session wouldn’t give up– and if the deadlock manager didn’t break the deadlock, they’d be stuck there forever.

I didn’t set the deadlock priority on any of my transactions, so the deadlock manager picked the session that it thought would be the least work to roll back– and it became the victim.

Read on for the scripts and also some hints to help you learn more about deadlocks.

Comments closed

Database Containment Checks

Parikshit Savjani discusses a nice little performance optimization in the latest versions of SQL Server 2014 and 2016:

Starting SQL 2014 SP1 CU8, SQL 2014 SP2 CU1 and SQL 2016 CU1, the spinlock to check the database containment property is replaced by the “load acquire and store release” lock semantics, which is a non-blocking lock-free synchronization mechanism between the concurrent threads. This avoids exclusive spinlocks and thereby avoids the spinlock collisions between the concurrent threads executing stored procedures from same database as described earlier. This change improves the overall concurrency and scalability of the system especially if all the worker threads are simultaneously executing a stored procedure from same database.

On extremely busy systems, this might be a reason to update.

Comments closed

Solr Lock Contention

Michael Sun shows how the Apache Solr team found and fixed a performance issue in their code:

Based on this testing, lock contention, which usually results in a performance bottleneck and underutilized resources, was our first “suspect.” We knew that using a commercial Java profiler, such as Yourkit, JProfiler and Java Flight Recorder, would help easily identify locks and determine how much time threads spend waiting on them. Meanwhile, the team had built custom infrastructure that allows one to run experiments with a profiler attached via a single command-line parameter.

In my own testing, the profiler data indeed revealed some contention particularly related to VersionBucket andHdfsUpdateLog locks, leading to long thread wait time. Although promisingly, this result corresponded somewhat to the description in SOLR-6820, nothing actionable resulted from the experiment.

I like these sorts of case studies because example is the school of mankind.  In this particular case, I really like the methodical approach, using available information to search for a root cause.  Some of the things Michael calls “false starts” I would consider to be initial steps:  checking OS, filesystem, and garbage collection metrics are important even in a case like this in which they did not lead to the culprit, as they help you eliminate suspects.

Comments closed

Testing Concurrency

Kendra Little walks through using OStress to test concurrency:

Even though I’m taking out UPDLOCKS, the following race condition pattern can still occur

  • Session A takes out an UPDLOCK, sees that key 266 does not exist, releases its UPDLOCK, and prepares to insert
  • Session B takes out an UPDLOCK, sees that key 266 does not exist, releases its UPDLOCK, and prepares to insert
  • Session A runs its  insert
  • Session B attempts to run its insert, but fails because of a duplicate key error

We need to hold that lock.

Understanding concurrency is one of the toughest parts of being a database developer, especially because it’s historically been difficult to test it.  I like what Kendra’s done here, making the process easy to follow.

Comments closed

NOLOCK Bug

Brent Ozar reports on a NOLOCK bug in SQL Server 2014 SP1 CU6:

  • While one transaction is holding an exclusive lock on an object (Ex. ongoing table update), another transaction is executing parallelized SELECT (…) FROM SourceTable, using the NOLOCK hint, under the default SQL Server lock-based isolation level or higher. In this scenario, the SELECT query trying to access SourceTable will be blocked.

  • Executing a parallelized SELECT (…) INTO Table FROM SourceTable statement, specifically using the NOLOCK hint, under the default SQL Server lock-based isolation level or higher. In this scenario, other queries trying to access SourceTable will be blocked.

The current recommendation is not to install CU6 until the issue is fixed.

Comments closed

Blocking Merge Statement

Kendra Little walks through the MERGE command and potential blocking issues with it:

The holdlock hint is a way to get serializable isolation level in SQL Server for a specific table, without having to change the isolation level for your entire session. Serializable is the highest isolation level in SQL Server using pessimistic locking.

When you “HOLDLOCK”, you tell SQL Server to protect any rows you read with a range lock– just in case someone comes along and tries to change one or sneak  one in.

That means that even when you’re just reading ParentTable and not inserting a row, you’re taking out a key range lock. You’re willing to fight other users over those rows to protect your statement.

Kendra’s final advice is to avoid the MERGE command in most cases; read on to find out why.

Comments closed

Deadlocks Aren’t Blocks

Dave Mason shows the difference between a blocked process and a deadlocked process:

Sometimes our end users may not know the difference between a deadlock and blocking. In fact, there are some that may use these two interchangeably, thinking they are the same thing. A quick demonstration is often helpful to show the difference. Here’s a simple example that uses two global temp tables.

NOTE: I want to stress that this is merely a simple example, and not a deep dive or exhaustive article. Books have been written on concurrency, including one by Kalen Delaney (b|t). It’s free, and highly recommended.

Ultimately, blocked processes can complete (once the blocker finishes its work); a set of deadlocked processes can never complete, which is why (at least) one of the deadlocked processes needs to be rolled back.  I like Dave’s “show me” approach here.

Comments closed

ENCRYPTION_SCAN Locks

Suresh Kandoth explains ENCRYPTION_SCAN in a non-TDE scenario:

There are three types of operations that acquire lock with the resource_type of DATABASE and resource_subtype of ENCRYPTION_SCAN:

– Encryption scan performed during TDE enable/disable

– Bulk Allocations that happen as part of bcp/bulk insert/select-into/index operations, etc

– Sort spills that are done as part of sort operators in the query plan

These locks are taken to serialize operations like bulk allocations and sorts with encryption scan.

Read the whole thing.  It looks like this isn’t by itself a significant issue, but it is interesting to see this lock type against a database without TDE.

Comments closed

Locking Basics

Jason Brimhall has an introductory-level discussion of locking in SQL Server:

A fundamental component of SQL Server is locking and locks. Locks within SQL Server are critical to the proper functioning of the database and the integrity of the data within the database. The presence of locks does not inherently mean there is a problem. In no way should locking within SQL Server be considered a monster, though locks may often times be misconstrued in that light.

This is an introductory-level discussion, so it doesn’t include optimistic concurrency or snapshot/RCSI, but if you’re unfamiliar with pessimistic concurrency, this is a good place to start.

Comments closed