Questions tagged [nolock]
NOLOCK is a SQL Server table hint, equivalent to READUNCOMMITTED. It applies READ UNCOMMITTED isolation level semantics to the hinted table.
28
questions
7
votes
1
answer
2k
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 ...
- 149
0
votes
3
answers
172
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 `...
-1
votes
1
answer
477
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 ...
- 3,360
0
votes
1
answer
636
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 ...
- 3,360
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 ...
- 3,360
0
votes
1
answer
75
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 ...
- 141
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 ...
- 213
4
votes
1
answer
140
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...
- 785
1
vote
1
answer
1k
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 ...
- 864
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 ...
- 1,898
5
votes
3
answers
11k
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 ...
- 151
5
votes
3
answers
707
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
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, ...
- 353
1
vote
2
answers
9k
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?
- 11
2
votes
0
answers
818
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 ...
- 121
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 ...
- 343
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 ...
- 4,727
1
vote
1
answer
681
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 ...
- 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 ...
- 111
3
votes
0
answers
730
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 ...
8
votes
1
answer
16k
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 ...
- 869
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 ...
- 135
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.....
- 107
5
votes
1
answer
484
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 ...
- 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
...
- 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 ...
- 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 ...
- 385
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 ...
- 7,081