Sql Server Table Locks in Long Query - Solution: Nolock

SQL Server Table locks in long query - Solution: NoLock?

Try looking at READ COMMITTED SNAPSHOT rather than NOLOCK. This means the data could be "old" but will never be dirty.

Does NOLOCK hint slow down operation?

The short answer to the question as stated is: "No."

In most cases the NOLOCK hint will speed up the query in question, as well as, any other queries operating against the specified table at the same time. The reason is that no locks are checked or obtained. You've listed the possible side effects in your question so I won't cover those here.

At the end of the day the query will be faster, but the results will be suspect.

Understanding SQL Server LOCKS on SELECT queries

A SELECT in SQL Server will place a shared lock on a table row - and a second SELECT would also require a shared lock, and those are compatible with one another.

So no - one SELECT cannot block another SELECT.

What the WITH (NOLOCK) query hint is used for is to be able to read data that's in the process of being inserted (by another connection) and that hasn't been committed yet.

Without that query hint, a SELECT might be blocked reading a table by an ongoing INSERT (or UPDATE) statement that places an exclusive lock on rows (or possibly a whole table), until that operation's transaction has been committed (or rolled back).

Problem of the WITH (NOLOCK) hint is: you might be reading data rows that aren't going to be inserted at all, in the end (if the INSERT transaction is rolled back) - so your e.g. report might show data that's never really been committed to the database.

There's another query hint that might be useful - WITH (READPAST). This instructs the SELECT command to just skip any rows that it attempts to read and that are locked exclusively. The SELECT will not block, and it will not read any "dirty" un-committed data - but it might skip some rows, e.g. not show all your rows in the table.

SQL Server DB Locks - select with nolock

The documentation states

All lock hints are propagated to all the tables and views that are
accessed by the query plan, including tables and views referenced in a
view.

This is easy to test.

Setup

CREATE DATABASE Testing

GO

ALTER DATABASE Testing SET READ_COMMITTED_SNAPSHOT  OFF

GO

USE Testing 

GO

CREATE TABLE dbo.Demo(X int);

INSERT INTO dbo.Demo VALUES (1), (2), (3);

go

CREATE VIEW dbo.[Inner] AS 
SELECT *
FROM dbo.Demo

GO

CREATE VIEW dbo.[Outer] AS 
SELECT *
FROM dbo.[Inner]
WITH (NOLOCK)

Connection 1 (leaves a transaction open taking a lock and an uncommitted row)

BEGIN TRAN

INSERT INTO dbo.Demo VALUES (4);

Connection 2

SELECT *
FROM dbo.[Outer] 

Returns

X
-----------
1
2
3
4

Showing the NOLOCK hint was propagated down and it read value 4 from the uncommitted transaction. (Selecting from dbo.[Inner] is blocked as expected)

SQL Server: VIEW with NO LOCK but called without NO LOCK

This is very easy to test. Firstly, in a sandbox environment, run the following:

CREATE TABLE dbo.MyTable (ID int);
GO

CREATE VIEW dbo.MyView AS

    SELECT ID
    FROM dbo.MyTable WITH (NOLOCK);
GO

CREATE VIEW dbo.MyView2 AS

    SELECT ID
    FROM dbo.MyTable;
GO

BEGIN TRANSACTION Test;

    INSERT INTO dbo.MyTable
    VALUES(1);

Notice I don't COMMIT the transaction. Now in a new window, run SELECT * FROM dbo.MyView;. Notice it returns results. If you also try SELECT * FROM dbo.MyView2 WITH (NOLOCK); You'll also get results. Try SELECT * FROM dbo.MyView2;, however, and the query will "hang".

You can then "clean up" by returning to your original query window and running the following:

COMMIT;
GO

DROP VIEW dbo.MyView2;
DROP VIEW dbo.MyView;
DROP TABLE dbo.MyTable;

Of course, the real question is, do you need NOLOCK, but that isn't what this question is about.

What is with (nolock) in SQL Server?

WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In a banking application with high transaction rates, it's probably not going to be the right solution to whatever problem you're trying to solve with it IMHO.

Select with (nolock)

Nolocks should be used with extreme caution. The most common understanding of nolock (read uncommitted) hint is that it reads data that has not been committed yet. However, there are other side effects that can be very dangerous. (search for "nolock" and "page splits")

There's a really good write up here... https://www.itprotoday.com/sql-server/beware-nolock-hint

In short, "nolocking"ing everything is not always a good idea... if ever.

SQL: NOLOCK causes query slow down

NOLOCK hint allows Allocation Order Scans. As such, they may create a completely different execution plan, one expected to be faster but that it turns out to be slower (eg. wrong cardinality estimates due to stale stats). As with any performance pro0blem, use an investigation methodology to find the cause of the problem. Waits and Queues is an excellent such methodology.

---

Related Topics