Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [nolock]

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

0
votes
0answers
42 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 ...
2
votes
3answers
2k 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 ...
5
votes
3answers
650 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 ...
24
votes
1answer
3k 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, ...
1
vote
2answers
3k 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?
2
votes
0answers
540 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 ...
5
votes
1answer
626 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 ...
3
votes
2answers
834 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 ...
1
vote
1answer
432 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 ...
10
votes
1answer
2k 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 ...
3
votes
0answers
407 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 ...
5
votes
1answer
10k 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 ...
0
votes
0answers
146 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 ...
2
votes
3answers
2k 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 ...
3
votes
1answer
2k 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.....
4
votes
1answer
305 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 ...
4
votes
2answers
6k 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 ...
34
votes
4answers
4k 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 ...
10
votes
1answer
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 ...
23
votes
3answers
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 ...