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
1 vote
1 answer
49 views

Does a commit occur all at same time from a transactional point of view?

Assume a "Bank_Account" table exists and contains a single column "balance", where everyone has balance=100. If a transaction performs "update Bank_Account set balance = 200 ...
user1508072's user avatar
-2 votes
2 answers
152 views

I've TABLOCKed a table. How can I read it?

I've TABLOCKed a table so I can run bulk inserts. How can I read it while it is still locked? Under normal circumstances, I would call this obviously impossible. Luckily, there is one key factor that ...
J. Mini's user avatar
  • 135
7 votes
1 answer
3k views

Does With(NoLock) help with query performance?

Most of the select statements in our stored procedures use WITH(NOLOCK) and I was told by my co-workers that they use it to improve performance. Now, I know that this hint helps with locking but does ...
RobMartin's user avatar
  • 149
0 votes
3 answers
287 views

mysql table get lock on adding new field

We want to add new field into table but table got lot its take 3 min on stage env but in production its taking more then 10 min(we killed the process after this) ALTER TABLE `posts_topic` ADD COLUMN `...
NARESH KUMAR TELI's user avatar
-1 votes
1 answer
613 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 ...
variable's user avatar
  • 3,541
0 votes
1 answer
763 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 ...
variable's user avatar
  • 3,541
7 votes
1 answer
2k 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 ...
variable's user avatar
  • 3,541
0 votes
1 answer
99 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 ...
InterLinked's user avatar
9 votes
2 answers
3k 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 ...
trademark's user avatar
  • 213
4 votes
1 answer
160 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...
xhr489's user avatar
  • 805
2 votes
1 answer
2k 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 ...
SHR's user avatar
  • 874
0 votes
0 answers
3k 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 ...
SE1986's user avatar
  • 1,950
5 votes
3 answers
12k 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 ...
AKS's user avatar
  • 151
5 votes
3 answers
724 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 ...
Dimitrios Desyllas's user avatar
24 votes
1 answer
5k 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, ...
Brian's user avatar
  • 353
1 vote
2 answers
10k 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?
rodf1021's user avatar
2 votes
0 answers
835 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 ...
Underscore's 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 ...
user238855's 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 ...
Magier's user avatar
  • 4,777
1 vote
1 answer
686 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 ...
mouliin's user avatar
  • 522
11 votes
1 answer
5k 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 ...
wookie23's user avatar
  • 111
3 votes
0 answers
762 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 ...
Rodrigo Rivera's user avatar
9 votes
1 answer
17k 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 ...
Jeroen's user avatar
  • 879
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 ...
SpeaksBinary's 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.....
kanu's user avatar
  • 107
5 votes
1 answer
489 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 ...
blakmk's 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 ...
King Chan's 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 ...
DataGirl's user avatar
  • 465
10 votes
2 answers
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 ...
Ciaran Archer's 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 ...
datagod's user avatar
  • 7,091