Finding problematic NOLOCK patterns in SQL Server - Part 1

By:   |   Updated: 2021-07-28   |   Comments   |   Related: 1 | 2 | 3 | 4 | > Locking and Blocking


Problem

I've warned before about the possible downsides of both NOLOCK in general and, more specifically, when used against the target of an update or delete. While Microsoft claims that corruption errors due to the latter have been fixed in cumulative updates (e.g. see KB #2878968), we're still seeing an occasional related issue where SQL Server will terminate, producing a stack dump that indicates a DML statement with NOLOCK as the cause. How do I find and correct all these potentially problematic statements?

Solution

Just as a tangible example, here is a stack dump with some data stripped out. The statement was longer than this, but it was really just an update using an inner join and a NOLOCK on both tables, including the target of the update:

***Stack Dump being sent to ...\LOG\SQLDump0018.txt
SqlDumpExceptionHandler: Process 7391 generated fatal exception c000000d EXCEPTION_INVALID_CRT_PARAMETER. 
SQL Server is terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
*   06/01/21 17:25:40 spid 7391
*
*   Exception Address = 00007FFAA7057AFC Module(KERNELBASE+0000000000007AFC)
*   Exception Code    = c000000d EXCEPTION_INVALID_CRT_PARAMETER
* Input Buffer 255 bytes –
*  U P D A T E   o p  … hexy stuff over here
*              S E T  …
*  x = 1     F R O M  …
*            d b o .  …
*  s o m e t h i n g  …
*  a s     o p     W  …
*  I T H   ( N O L O  …
*  C K )              …
…
* Short Stack Dump
00007FFAA7057AFC Module(KERNELBASE+0000000000007AFC)
00007FFA9E0CDCD9 Module(MSVCR120+000000000006DCD9)
00007FFA9E09647B Module(MSVCR120+000000000003647B)
00007FFA7370B83B Module(sqlmin+000000000119B83B)
…
00007FFA76161F87 Module(sqlTsEs+0000000000001F87)
…
00007FFA6FFA2DE3 Module(sqllang+0000000000012DE3)
…
00007FFA87B67D88 Module(sqldk+0000000000027D88)
00007FFAA9771412 Module(KERNEL32+0000000000001412)
00007FFAA9E654F4 Module(ntdll+00000000000154F4)
Stack Signature for the dump is 0x00000000CB972451

These are the types of statements I want to eliminate from our codebase, since they are potentially ticking time bombs waiting to create an outage. However, unless they are directly indicated in a stack dump, it is going to be very tedious to track them down. While it may seem that this should be straightforward, it is not a simple string parsing or regular expressions problem. Depending on the scope of your system, merely finding stored procedures or triggers containing the following simple patterns may produce a lot of false positives:

WHERE LOWER(object_definition(object_id)) LIKE N'%update%from%nolock%'
WHERE LOWER(object_definition(object_id)) LIKE N'%delete%from%nolock%'

Not only because there is no reliable way to ensure those keywords all appear within the same statement, but also because any part of that pattern could be contained in a comment, an object name, or an alias. Consider that T-SQL is an extremely complex and forgiving language, making it legal to do things like this:

SELECT name FROM sys.objects [WITH (NOLOCK)];

Note that the NOLOCK there is an alias and not a table hint; which means you could do this (not that you would):

SELECT [WITH (NOLOCK)].name FROM sys.objects [WITH (NOLOCK)] WITH (NOLOCK);

Vanilla string parsing and/or RegEx will have a hard time figuring out that one of those statements does not actually use a NOLOCK hint. I'm sure you could exert a lot of effort to write something that catches some cases, but I would like something that is both more simple and more reliable.

My strategy was to use this type of brute force query to cast an initially wide net, but then once I narrowed down that set of matching modules, to be a lot more surgical – only identifying the statements inside those modules that exhibit the problem we're solving. Note that I am only pursuing the very basic forms of this type of update or delete, and there are other more complex forms (e.g. involving CTEs) that I haven't solved quite yet.

In the past, I have used TSqlFragmentVisitor to approach similar problems where I needed something a little more precise than string parsing. In this case, we can absolutely identify when the presence of NOLOCK in an update or delete statement is actually a table hint, and we can also determine whether that hint applies to the target of the statement.

Let's start with a minimal PowerShell script that uses a Visitor pattern to walk through a single statement and output all of the tokens found in that statement. You will need Microsoft.SqlServer.TransactSql.ScriptDom.dll somewhere on your machine; you can get this file via sqlpackage from Microsoft.

Add-Type -Path "<path>\Microsoft.SqlServer.TransactSql.ScriptDom.dll";
$Parser = [Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser]($true)::New(); 
$Errors = [System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]]::New();
$Body = "UPDATE x SET x += 1 FROM dbo.x WITH (NOLOCK);";
$Fragment = $Parser.Parse([System.IO.StringReader]::New($Body), [ref]$Errors);
$Fragment.Accept([Visitor]::New());
class Visitor: Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor 
{
  [void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragment] $Fragment)
  {
    $FragmentType   = $Fragment.GetType().Name;
    $ThisTokenStart = $Fragment.FirstTokenIndex;
    $ThisTokenText  = $Fragment.ScriptTokenStream[$ThisTokenStart].Text;
    Write-Host "TokenID $($ThisTokenStart): $($FragmentType): $($ThisTokenText)";
  }
}

Output:

TokenID 0:  TSqlScript: UPDATE
TokenID 0:  TSqlBatch: UPDATE
TokenID 0:  UpdateStatement: UPDATE
TokenID 0:  UpdateSpecification: UPDATE
TokenID 2:  NamedTableReference: x
TokenID 2:  SchemaObjectName: x
TokenID 2:  Identifier: x
TokenID 12: FromClause: FROM
TokenID 14: NamedTableReference: dbo
TokenID 14: SchemaObjectName: dbo
TokenID 14: Identifier: dbo
TokenID 16: Identifier: x
TokenID 21: TableHint: NOLOCK
TokenID 6:  AssignmentSetClause: x
TokenID 6:  ColumnReferenceExpression: x
TokenID 6:  MultiPartIdentifier: x
TokenID 6:  Identifier: x
TokenID 10: IntegerLiteral: 1

Note there is not really any tangible looping here, so there is no direct way to recall any of the properties or state from prior tokens that were "visited." Also, you'll notice the output is not necessarily going to be in any logical or expected order.

If we change the string to:

$Body = "DELETE x FROM dbo.x WITH (NOLOCK);";

The output becomes:

TokenID 0:  TSqlScript: DELETE
TokenID 0:  TSqlBatch: DELETE
TokenID 0:  DeleteStatement: DELETE
TokenID 0:  DeleteSpecification: DELETE
TokenID 2:  NamedTableReference: x
TokenID 2:  SchemaObjectName: x
TokenID 2:  Identifier: x
TokenID 4:  FromClause: FROM
TokenID 6:  NamedTableReference: dbo
TokenID 6:  SchemaObjectName: dbo
TokenID 6:  Identifier: dbo
TokenID 8:  Identifier: x
TokenID 13: TableHint: NOLOCK

Some of these tokens are important, while others are redundant or irrelevant. Of particular interest are:

  • UpdateStatement / DeleteStatement – these contain all the tokens that represent the entire statement, including the starting and ending token (so we know when a token doesn't belong to this statement)
  • Identifier – this is any object or alias
  • FromClause – for a delete statement, this is how we know the previous identifier must be the target
  • AssignmentSetClause – for an update statement, this is more reliable than the from as an indicator, since the set clause can contain identifiers
  • TableHint – this is how we can be sure an instance of NOLOCK is actually a table hint

Now, let's start adding some analysis to that output, so we can determine how we are going to identify only the statements that exhibit all of our criteria – an update or delete statement that uses a NOLOCK hint against the target table. First, we need to be able to determine (and keep track of) what type of statement it is, which identifier represents the target of the update or delete, and which objects or aliases are associated with any NOLOCK hints.

Add-Type -Path "C:\temp\ParseNOLOCK\Microsoft.SqlServer.TransactSql.ScriptDom.dll";
$Parser = [Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser]($true)::New(); 
$Errors = [System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]]::New();
$Body = "UPDATE x SET x += 1 FROM dbo.x WITH (NOLOCK);";
$Fragment = $Parser.Parse([System.IO.StringReader]::New($Body), [ref]$Errors);
$Fragment.Accept([Visitor]::New());
$global:StatementType = "";
class Visitor: Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor 
{
  [void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragment] $Fragment)
  {
    $FragmentType   = $Fragment.GetType().Name;
    $ThisTokenStart = $Fragment.FirstTokenIndex;
    $ThisTokenEnd   = $Fragment.LastTokenIndex;
    $ThisTokenText  = $Fragment.ScriptTokenStream[$ThisTokenStart].Text;
    if ($FragmentType -in ("UpdateStatement", "DeleteStatement"))
    {
        $ThisStatement = "";
        $global:StatementType = $FragmentType.Substring(0,6).ToLower();
        for ($i = $ThisTokenStart; $i -le $ThisTokenEnd; $i++)
        {
            $ThisStatement += $Fragment.ScriptTokenStream[$i].Text;
        }
        Write-Host "New $($global:StatementType) statement: $($ThisStatement)";
    }
    if ($FragmentType -in ("Identifier","FromClause","UpdateStatement","DeleteStatement","AssignmentSetClause"))
    {
        Write-Host " - TokenID $($ThisTokenStart): ($($FragmentType)): $($ThisTokenText)";
        if ((($global:StatementType -eq "update") -and ($FragmentType -eq "AssignmentSetClause")) -or 
            (($global:StatementType -eq "delete") -and ($FragmentType -eq "FromClause")))
        {
            Write-Host "Target object / alias is the highest Identifier TokenID before $($ThisTokenStart)."
        }
    } 
    if (($FragmentType -eq "TableHint") -and ($ThisTokenText -ieq "NOLOCK"))
    {
        Write-Host " - TokenID $($ThisTokenStart) ($($FragmentType)): $($ThisTokenText)";
        Write-Host "NOLOCK applies to the highest Identifier TokenID before $($ThisTokenStart).";
    }
  }
}

Output:

New update statement: UPDATE x SET x += 1 FROM dbo.x (NOLOCK);
 - TokenID 0:  (UpdateStatement): UPDATE
 - TokenID 2:  (Identifier): x
 - TokenID 12: (FromClause): FROM
 - TokenID 14: (Identifier): dbo
 - TokenID 16: (Identifier): x
 - TokenID 21: (TableHint): NOLOCK
NOLOCK applies to the highest Identifier TokenID before 21.
 - TokenID 6:  (AssignmentSetClause): x
Target object / alias is the highest Identifier TokenID before 6.
 - TokenID 6:  (Identifier): x

In this standalone case, it's simple to visually inspect the output and determine that the target object is x (token 2, the highest identifier prior to token 6), that the NOLOCK hint applies to the object named x (token 16, the highest identifier before token 19), and that these identifier names match – giving us a "hit."

What if the batch has multiple statements?

$Body = "UPDATE x SET x += 2 FROM dbo.x WITH (NOLOCK);
         DELETE y FROM dbo.y AS y WITH (NOLOCK);";

Output:

New update statement: UPDATE x SET x += 2 FROM dbo.x WITH (NOLOCK);
 - TokenID 0:  (UpdateStatement): UPDATE
 - TokenID 2:  (Identifier): x
 - TokenID 12: (FromClause): FROM
 - TokenID 14: (Identifier): dbo
 - TokenID 16: (Identifier): x
 - TokenID 21: (TableHint): NOLOCK
NOLOCK applies to the highest Identifier TokenID before 21.
 - TokenID 6: (AssignmentSetClause): x
Target object / alias is the highest Identifier TokenID before 6.
 - TokenID 6: (Identifier): x
New delete statement: DELETE y FROM dbo.y AS y WITH (NOLOCK);
 - TokenID 25: (DeleteStatement): DELETE
 - TokenID 27: (Identifier): y
 - TokenID 29: (FromClause): FROM
Target object / alias is the highest Identifier TokenID before 29.
 - TokenID 31: (Identifier): dbo
 - TokenID 33: (Identifier): y
 - TokenID 37: (Identifier): y
 - TokenID 42: (TableHint): NOLOCK
NOLOCK applies to the highest Identifier TokenID before 42.

At this level of complexity, it is still easy to visually identify both of these statements as "hits" that need to be corrected.

At scale, though, this approach won't work. For one, you can't tackle this visually if you have hundreds or thousands of objects in your wide net. It can also be error-prone to visually inspect output like this and eliminate false positives, especially when queries are longer, more complex, and use inconsistent or confusing table aliasing.

In the next parts of this series, I'll show how I collect all the module bodies across the enterprise and put the output into tables that we can query for better precision. I'll also show counterexamples to drive home that no method is going to be 100% foolproof.

Next Steps

See these tips and other resources involving NOLOCK, parsing modules, and TSqlFragmentVisitor:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2021-07-28

Comments For This Article

















get free sql tips
agree to terms