Understanding the SQL Server NOLOCK hint

 

Problem

I see the use of the NOLOCK hint in existing code for my stored procedures and I am not exactly sure if this is helpful or not. It seems like this has been a practice that was put in place and now is throughout all of the code wherever there are SELECT statements. Can you explain the what NOLOCK does and whether this is a good practice or not?

Solution

It seems that in some SQL Server shops the use of the NOLOCK (aka READUNCOMMITED) hint is used throughout the application. In this tip we take a closer look at how this works and what the issues maybe when using NOLOCK.

What does the SQL Server NOLOCK hint do?

• The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes.
• This can improve query performance, but also introduces the possibility of dirty reads.
• Read more to better understand the use of NOLOCK.

Example of SQL Server NOLOCK

Let's walk through some simple examples to see how this works. (These queries are run against the AdventureWorks database.)

Here is a query that returns all of the data from the Person.Contact table. If I run this query I can see there is only one record that has a Suffix value for ContactID = 12.

SELECT * FROM Person.Contact WHERE ContactID < 20

Let's say another user runs the below query in a transaction. The query completes and updates the records, but it is not yet committed to the database so the records are locked.

-- run in query window 1
BEGIN TRAN
UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID < 20
-- ROLLBACK or COMMIT

If I run the same query from above again you will notice that it never completes, because the UPDATE has not yet been committed.

-- run in query window 2
SELECT * FROM Person.Contact WHERE ContactID < 20

If I run sp_who2 I can see that the SELECT statement is being blocked. I will need to either cancel this query or COMMIT or ROLLBACK the query in window one for this to complete. For this example I am going to cancel the SELECT query.

To get around the locked records, I can use the NOLOCK hint as shown below and the query will complete even though the query in window 1 is still running and has not been committed or rolled back.

-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20 

If you notice below the Suffix column now has "B" for all records. This is because the UPDATE in window 1 updated these records. Even though that transaction has not been committed, since we are using the NOLOCK hint SQL Server ignores the locks and returns the data. If the UPDATE is rolled back the data will revert back to what it looked like before, so this is considered a Dirty Read because this data may or may not exist depending on the final outcome in query window 1.

If I rollback the UPDATE using the ROLLBACK command and rerun the SELECT query we can see the Suffix is back to what it looked like before.

-- run in query window 1
ROLLBACK

-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20 
-- or
SELECT * FROM Person.Contact WHERE ContactID < 20

So the issue with using the NOLOCK hint is that there is the possibility of reading data that has been changed, but not yet committed to the database. If you are running reports and do not care if the data might be off then this is not an issue, but if you are creating transactions where the data needs to be in a consistent state you can see how the NOLOCK hint could return false data.

Types of SQL Server Locks Used with NOLOCK

So what kind of locking is used when the NOLOCK hint is used.

If we run our SELECT without NOLOCK we can see the locks that are taken if we use sp_lock. (To get the lock information I ran sp_lock in another query window while this was running.)

SELECT * FROM Person.Contact WHERE ContactID < 20 

If we do the same for our SELECT with the NOLOCK we can see these locks.

SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20 

The differences are that there is a "S" shared access lock that is put on the page (PAG) that we are reading for the first 19 rows of data in the table when we don't use NOLOCK. Also, we are getting a Sch-S lock versus an IS lock for the table (TAB).

So another thing to point out is that even when you just SELECT data SQL Server still creates a lock to make sure the data is consistent.

These are the lock types and the lock modes that are used for the above two queries.

Lock Types

• MD - metadata lock
• DB - database lock
• TAB - table lock
• PAG - page lock

Mode

• S - Shared access
• Sch-S - Schema stability makes sure the schema is not changed while object is in use
• IS - Intent shared indicates intention to use S locks

SQL Server READUNCOMMITED and NOLOCK

The NOLOCK hint is the same as the READUNCOMMITED hint and can be used as follows with the same results.

SELECT * FROM Person.Contact WITH (READUNCOMMITTED) 

SELECT statements only with NOLOCK

The NOLOCK and READUNCOMMITED hints are only allowed with SELECT statements. If we try to use this for an UPDATE, DELETE or INSERT we will get an error.

UPDATE Person.Contact with (NOLOCK) SET Suffix = 'B' WHERE ContactID < 20

You will see this error.

Msg 1065, Level 15, State 1, Line 15
The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.

Schema Change Blocking with NOLOCK

Since a NOLOCK hint needs to get a Sch-S (schema stability) lock, a SELECT using NOLOCK could still be blocked if a table is being altered and not committed. Here is an example.

-- run in query window 1
BEGIN TRAN
ALTER TABLE Person.Contact ADD column_b 
VARCHAR(20) NULL ; 

If we try to run our SELECT statement it will be blocked until the above is committed or rolled back.

-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20 

Issues with NOLOCK

We mentioned above how you can get dirty reads using the NOLOCK hint. These are also other terms you may encounter for this hint.

• Dirty Reads - this occurs when updates are done, so the data you select could be different.
• Nonrepeatable Reads - this occurs when you need to read the data more than once and the data changes during that process
• Phantom Reads - occurs where data is inserted or deleted and the transaction is rolled back. So for the insert you will get more records and for the delete you will get less records.

To learn more about these issues read this article: http://msdn.microsoft.com/en-us/library/ms190805.aspx

Isolation Level

You can also set the Isolation Level for all queries instead of using the NOLOCK or READUNCOMMITTED hint. The isolation level will apply the READUNCOMMITTED to all SELECT statements that are performed from when this is turned on until it is turned off.

In the example below, the two SELECT statements will use the READUNCOMMITED or NOLOCK hint and the UPDATE will still function as normal. This way you can set a whole batch of statements instead of modifying each query.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on

SELECT * FROM Person.Contact WHERE ContactID < 20 

UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID = 1 

SELECT * FROM Person.Contact WHERE ContactID < 20 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off

Entity Framework Core: DbContextOptionsBuilder does not contain a definition for 'usesqlserver' and no extension method 'usesqlserver'

 I am new to EF core and I'm trying to get it to work with my ASP.NET Core project.

I get the above error in my startup.cs when trying configure the DbContext to use a connection string from config. I am following this tutorial.

The problematic code is in startup.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.SpaServices.Webpack;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.EntityFrameworkCore;
using tracV2.models;
using tracV2.data;

namespace tracV2
{
    public class Startup
    {
        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            // Add framework services.
            services.AddMvc();

            services.AddSingleton<IConfiguration>(Configuration);

            string conn = Configuration.GetConnectionString("optimumDB");

            services.AddDbContext<tracContext>(options => options.usesqlserver(conn));
        }

The UseSqlServer method is recognized if I put it directly into the context:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace tracV2.data
{
    public class tracContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("myrealconnectionstring");
        }

All my research online points to missing references, but I can't seem to find out which one I am missing

================================================================

Solutions:

First we install the Microsoft.EntityFrameworkCore.SqlServer NuGet Package:

PM > Install-Package Microsoft.EntityFrameworkCore.SqlServer

Then, after importing the namespace with

using Microsoft.EntityFrameworkCore;

we add the database context:

services.AddDbContext<AspDbContext>(options =>
    options.UseSqlServer(config.GetConnectionString("optimumDB")));

How to retry a subscription when it fails in Angular 9?

 Context: Working with Angular 9. I'm trying to wait the back-end to be available to receive requests. Meanwhile it's not, there is a progress bar loading.

Issue: when the back-end is not yet available, the subscription fails immediately and enters the (error) callback function of the method .subscribe(), with a message like Http failure response for http://localhost:5555/api/info: 504 Gateway Timeout

I did research a bit, and the examples I've found modify the service class where httpClient.get is present, to retry x times the request, but I can't do that as the ts service is auto generated.

My 1st idea was to use while() loop and a flag, so each time (error) was executed, the flag would be false and then retry the subscription. But it would lead to a memory leak.

checkIfBackendAvailable() {
    var backendAvailable = false;
    while(!backendAvailable){
      let subscription = this.infoService.getInfo().subscribe(
      (info) => {
        if (info) {
            backendAvailable = true;
            this.progressBarValue = 100
            // do somethings
         }
       }
        ,
        (error) => {
          clearTimeout(this.infoTimeOut);
          this.showMessage("back-end not available");
          this.stopLoadingBar();
          //do somethings
        }
      );
    }

    this.infoTimeOut = setTimeout(() => {
      if (!backendAvailable) {
        subscription.unsubscribe()
        this.showMessage("error");
        this.stopLoadingBar();
      }
    }, 120000);
  }

=========================================================================

Ans:

This is the most close solution to what I want:

 checkBackend() {
    console.log('Waiting for backend...');
    
    let infoObservable = this.infoService.getInfo().pipe(
      retryWhen(errors =>
        errors.pipe(
          delay(10000),
          tap(errorStatus => {
            //stop timeout if error
            this.stopInfoTimeOut();
            //parse the error, depending on the return from the http.get
            if (!errorStatus.includes('504')) {
              throw errorStatus;
            }
            //run timeout when trying a new request
            this.runInfoTimeout();
          })
        )
      ))
      
    this.infoSubscription= infoObservable.subscribe({
      next: val => {
        if (val) {
          console.log('Backend available');
          this.stopInfoTimeOut();
        }
      },
      error: val => {
        console.log(val);
        this.stopInfoTimeOut();
        this.showMessage("Backend not available, try later");
      }, 
      complete: () => {}
    })

MatNativeDateModule, MatMomentDateModule not found in angular 9.1.1

 Old angular/material has those two modules. but angular/material 9.1.1 version has not those two module. I got below error. Anyone has any idea how to import those two module

Uncaught (in promise): Error: MatDatepicker: No provider found for DateAdapter. You must import one of the following modules at your application root: MatNativeDateModule, MatMomentDateModule, or provide a custom implementation.
Error: MatDatepicker: No provider found for DateAdapter. You must import one of the following modules at your application root: MatNativeDateModule, MatMomentDateModule, or provide a custom implementation.

Ans:

Angullar 8,9

import { MatDatepickerModule } from '@angular/material/datepicker';
import { MatNativeDateModule } from '@angular/material/core';

Angular 7 and below

import { MatDatepickerModule, MatNativeDateModule } from '@angular/material';

You need to import both MatDatepickerModule and MatNativeDateModule under imports and add MatDatepickerModule under providers

imports: [
    MatDatepickerModule,
    MatNativeDateModule 
  ],
  providers: [  
    MatDatepickerModule,
    MatNativeDateModule  
  ],

.NET Core 3.0 + Angular 9 cli application called via the SpaServices extensions hangs in common configuration #17277

 Describe the bug

Running a .NET Core 3.0 + Angular 9 cli (version 9.0.0-rc.2 as of this post) with the angular cli build option progress set to false will hang until the StartupTimeout expires (default: 120 seconds)

To Reproduce

I created a repo with a bare bones .NET Core 3.0 + Angular 9 cli application to demo the bug. Simply clone, npm build the ClientApp folder, and dotnet run. Or you can follow the steps below to do this from scratch:

1. Ensure you have .NET Core 3.0 installed
2. Create a .NET Core application with the current Angular templates by typing: dotnet new angular -n HelloWorld
3. Navigate to the ClientApp folder and install the Typescript helper functions required for Angular 9 by typing: npm install tslib@latest
4. Update to Angular 9 by typing: ng update @angular/core @angular/cli --next
5. Ensure the progress build option in the angular.json file is set to false
6. Run the application by navigating back to the root folder and typing: dotnet run
7. Launch a browser and navigate to: https://localhost:5001

The application will hang and eventually timeout.

Further technical details

This appears to be caused by a change in how ng serve outputs to the console in the new Angular 9 CLI. The AngularCliMiddleware makes a WaitForMatch() method call against the standard output to signify when the Angular assets have been generated and the web server is ready to receive requests (Line 84). However, unless the progress option is set to true in the angular.json file you never see the expected line.

UPDATE: Updated to .NET Core 3.1 and Angular 9.0.0-rc.5. Same issue. New, simpler workaround is to modify your npm start script to perform a simple echo prior to ng serve (see comment below)

UPDATE (6/5/2020): Lots of recent comments so I figured I'd share exactly what has worked for me with every release of .NET and Angular since my original post. Update your package.json like so:

  "scripts": {
    "ng": "ng",
    "start": "echo hello && ng serve",
    "build": "ng build",
    "test": "ng test",
    "lint": "ng lint",
    "e2e": "ng e2e"
  }