Questions tagged [nolock]

NOLOCK is a SQL Server table hint, equivalent to READUNCOMMITTED. It applies READ UNCOMMITTED isolation level semantics to the hinted table.

Filter by
Sorted by
Tagged with
0 votes
0 answers
45 views

Does S lock on HOBT (BULK_OPERATION) block writes and vice versa?

I am learning about using nolock with heap and notice that it takes S lock (BULK_OPERATION) on HOBT and Sch-S lock on OBJECT. Questions: Does S lock on HOBT (BULK_OPERATION) [taken when you select a ...
user avatar
  • 2,456
-1 votes
1 answer
185 views

How does creating an index help with NOLOCK problems?

Link: https://www.brentozar.com/archive/2018/10/using-nolock-heres-how-youll-get-the-wrong-query-results/ The article mentions some problems with using NOLOCK: You can see rows twice You can skip ...
user avatar
  • 2,456
0 votes
1 answer
343 views

Are missed rows and duplicate rows the symptoms due to allocation order scan or due to the no lock and page splits?

NOLOCK results in an allocation order scan rather than an index order scan. NOLOCK doesn't block writes because it doesn't take shared locks on the table. During the NOLOCK's scan, since there are no ...
user avatar
  • 2,456
7 votes
1 answer
729 views

What are the problems with using NOLOCK given an approach to handle the short comings?

I am researching the harm of using NOLOCK SQL to load data from one database that is actively used into a reporting database. I understand that there are problems with using NOLOCK but I am thinking ...
user avatar
  • 2,456
0 votes
1 answer
41 views

SELECT from table without blocking INSERTs into it?

I have an application where each page load inserts information about the visit into a MariaDB table. There is an analytics hub where statistics from this data can be viewed. In the beginning, this ...
user avatar
9 votes
2 answers
2k views

Safe to use WITH (NOLOCK) in this case?

Scenario I have a table that deals with concurrent SELECT's and DELETE's. I'm getting a few deadlocks on my SELECT statements. I assume that the DELETE from the other transaction is getting an ...
user avatar
  • 213
4 votes
1 answer
77 views

Skipped rows or rows read multiple times with NOLOCK

Is it possible to stimulate a situation where rows either get skipped or get read multiple times in a select statement? E.g. the way we can cause a blocking or a deadlock...
user avatar
  • 619
1 vote
1 answer
940 views

Can I prevent deadlock using `WITH(NOLOCK)`?

I have a table which I'm only Insert, Delete and Select (no Updates) on Insert, Sometimes it used a session lock (WITH (TABLOCKX)) I have a program using ADO which Select from the table using the ...
user avatar
  • 794
0 votes
0 answers
2k views

Perform SELECT without Locking Table but don't allow Dirty Reads

Is there a way I can perform a SELECT query which wont lock the table it is reading but also won't perform dirty reads? for example, assume Table1 has 100m records: SELECT Col1, Col2 FROM ...
user avatar
  • 1,463
5 votes
3 answers
9k views

How to avoid a select query for holding a Sch-S lock

I am looking for an option to avoid Sch-S locks when running a Select query. I have a database which is controlled by an application written by others as well as my own application. One of the tables ...
user avatar
  • 151
5 votes
3 answers
690 views

Why is NOLOCK implemented like that?

Recently I have searched for NOLOCK option in SQL Server. What I've discovered is that when a transaction is active on a table, SQL Server does not allow even reading from a specific table until the ...
user avatar
24 votes
1 answer
4k views

(NOLOCK) vs NOLOCK

I was investigating some blocking when I saw a query that looked something like this: SELECT SomeField FROM SomeTable NOLOCK I saw the NOLOCK and was curious how it could be blocking other queries, ...
user avatar
  • 353
1 vote
2 answers
8k views

SQL Server - NOLOCK vs. READONLY connections

For SQL Server 2008 and above, should (NOLOCK) be used on each table of a SELECT statement even if the connection is configured as READONLY?
user avatar
2 votes
0 answers
770 views

Does HOBT shared lock prevent writes

I am attempting to execute long running reads against a table which is also the target of insert bulk queries from sqlbulkcopy. In order to achieve this I have added the with(nolock) to the query ...
user avatar
8 votes
1 answer
1k views

How can I prove NOLOCK is the source of deadlocking problems?

I'm not trying to start a windows/mac type discussion. Personally, I don't need any convincing that NOLOCK is not a good idea as a reflexive practice. It seems when you're developing everything ...
user avatar
3 votes
2 answers
1k views

Why is WITH NOLOCK running as Serializable

I ran the following query: SELECT session_id,CASE transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 ...
user avatar
  • 4,645
1 vote
1 answer
646 views

SELECT Causes Performance Issue [closed]

I have a SQL SELECT statement that has joins on 27 of our warehouse tables. Original statement was taking more than an hour everyday so I decided to optimize it. I created some indexes but still it ...
user avatar
  • 522
11 votes
1 answer
4k views

How to reproduce "Could not continue scan with NOLOCK due to data movement"

I occasionally get "Could not continue scan with NOLOCK due to data movement" with some large jobs, which do have WITH (NOLOCK) on the select queries. I understand this has something to do with ...
user avatar
  • 111
3 votes
0 answers
586 views

Table queries locked during long insert SQL server

Everyday runs a sql job that loads data from bulk files and load them in auxiliary tables, to adapt and then copy to the destination table where applications read (and only read) the data. I am ...
user avatar
8 votes
1 answer
15k views

Use NOLOCK hint when calling table valued function

Suppose the following function: CREATE FUNCTION [dbo].[ufnTest]() RETURNS TABLE AS RETURN SELECT 1 AS Nr My actual function will select actual data from many different tables. I (mostly) understand ...
user avatar
  • 849
0 votes
0 answers
154 views

NOLOCK hint on a table that is 100% reads?

I have tables that during production hours are not wrote to, and yet during the night they can have records written. The few nights data is being wrote to the tables, users are not running queries ...
user avatar
  • 63
2 votes
3 answers
3k views

SQL Server data extract with limited performance impact

Background: I have a SQL Server 2008 Enterprise Edition database containing three tables. The tables contain ~3 million, ~14 million, ~14.5 million rows respectively. Problem: I need to do a data ...
user avatar
3 votes
1 answer
3k views

Execution of sequential delete and uncommitted read

We are running the following queries using dynamic SQL, which is erroring out for few cases when we run concurrent instances. The error is: Could not continue scan with NOLOCK due to data movement.....
user avatar
  • 107
5 votes
1 answer
447 views

repeatable read and nolock

Does anyone know what the net effect of having a block of t-sql with a transaction isolation level of repeatble read and then inside having a select with nolock. Does the nolock become invalid ...
user avatar
  • 51
4 votes
2 answers
7k views

Can someone explain why select with nolock will query a potion of updated data?

I was reading the answer from here (from stackoverflow, I think should ask in here) NOLOCK means placing no locks at all. Your query may returns portions of data as of before UPDATE and ...
user avatar
  • 399
34 votes
4 answers
5k views

Is NOLOCK always bad?

I am a Report Developer who wants to make my queries as efficient as possible. I used to work with a DBA who told me - I believe because I was always dealing with reports on a Production Server - to ...
user avatar
  • 465
10 votes
1 answer
25k views

Could not continue scan with NOLOCK due to data movement

We run SQL Server 2000 and we get a few of these errors every night. Could not continue scan with NOLOCK due to data movement The query that throws this error is a large complex query that joins ...
user avatar
23 votes
3 answers
5k views

Justify NOT using (nolock) hint in every query

Have you ever had to justify NOT using a query hint? I am seeing WITH (NOLOCK) in every single query that hits a very busy server. It is to the point that the developers think it should just be on ...
user avatar
  • 7,016