SELECT WITH (NOLOCK) sometimes returns no results

up vote
-1
down vote

favorite

The following query usually returns a large number of results (somewhere around ~750k results). However, in production, I encounter a sporadic situation where it returns 0 results. If we were to re-run it the next second after it returned 0, it would just return the results as normal.

I am sure that the data is still there and that there shouldn't be anything that would delete it...

I had a look in a few places and there's no way I could find what might be causing the issue mentioned above ... also, there doesn't seem to be an easy way to replicate it... it's just random.

As my SQL knowledge is quite limited, could you please think of certain scenarios where that might occur? Or maybe suggest easy ways of debugging this?

SELECT MAX(Table1.Cust_ID) AS cust_id, Table1.Email, MAX(Table1.Cust_Name) as cust_name

FROM dbo.Table1 WITH (NOLOCK)

INNER JOIN dbo.Table2 WITH (NOLOCK)

  ON Table1.id = Table2.id

  WHERE Table1.some_id = 100 AND Table1.some_value = 'test_value'

  AND  Table1.some_boolean_1 = 1

  AND  Table1.some_date_1 IS NULL

  AND (Table1.some_boolean_2 IS NULL OR Table1.some_boolean_2 = 1)

  AND (Table1.some_date_2 > '20171119' OR Table1.some_date_3 > '20171119' OR 

       Table2.some_date_3 > '20171119')

GROUP BY Table1.Email

Many thanks!

edited Nov 19 at 16:54

asked Nov 19 at 16:01

Catalin Lupuleti

178

Go line by line and check all those WHERE clauses and find where the records fall off? Query the tables every few seconds and confirm records are not being mass deleted?
– dfundako
Nov 19 at 16:04

9

This may well be "working as designed". NOLOCK can skip rows, repeat rows and (in older versions, at least) error out as a result of data movement, especially under load. If you need reliable results, don't use NOLOCK -- stick with READ COMMITTED, or use snapshot isolation. NOLOCK is only remotely useful for queries where the correct results don't really matter (for example, monitoring queries that will be re-run momentarily).
– Jeroen Mostert
Nov 19 at 16:06

4

Yes, nolock can skip rows. See dba.stackexchange.com/a/71640/5203 and the links from there.
– GSerg
Nov 19 at 16:06

2

NOLOCK performs a "raw" read on the page structures. It is not only not consistent on the row level, it's not consistent at all. It is absolutely possible for it to skip all rows you're interested in, if it comes in in the middle of a page split. Source: personal experience from a query that did a bunch of COUNT(*)s with a GROUP BY that suddenly started reporting that my table was completely empty when the server was under heavy load (when, obviously, it was not). This issue only occurred once, but it was serious enough that it justified a switch to snapshot isolation.
– Jeroen Mostert
Nov 19 at 16:15

1

Your query is syntactically incorrect - perhaps caused by your obfuscation attempts? You cannot select 3 columns and group by just one of those.
– SMor
Nov 19 at 16:41

|
show 5 more comments

up vote
-1
down vote

favorite

I am sure that the data is still there and that there shouldn't be anything that would delete it...

I had a look in a few places and there's no way I could find what might be causing the issue mentioned above ... also, there doesn't seem to be an easy way to replicate it... it's just random.

As my SQL knowledge is quite limited, could you please think of certain scenarios where that might occur? Or maybe suggest easy ways of debugging this?

SELECT MAX(Table1.Cust_ID) AS cust_id, Table1.Email, MAX(Table1.Cust_Name) as cust_name

FROM dbo.Table1 WITH (NOLOCK)

INNER JOIN dbo.Table2 WITH (NOLOCK)

  ON Table1.id = Table2.id

  WHERE Table1.some_id = 100 AND Table1.some_value = 'test_value'

  AND  Table1.some_boolean_1 = 1

  AND  Table1.some_date_1 IS NULL

  AND (Table1.some_boolean_2 IS NULL OR Table1.some_boolean_2 = 1)

  AND (Table1.some_date_2 > '20171119' OR Table1.some_date_3 > '20171119' OR 

       Table2.some_date_3 > '20171119')

GROUP BY Table1.Email

Many thanks!

edited Nov 19 at 16:54

asked Nov 19 at 16:01

Catalin Lupuleti

178

Go line by line and check all those WHERE clauses and find where the records fall off? Query the tables every few seconds and confirm records are not being mass deleted?
– dfundako
Nov 19 at 16:04

9

This may well be "working as designed". NOLOCK can skip rows, repeat rows and (in older versions, at least) error out as a result of data movement, especially under load. If you need reliable results, don't use NOLOCK -- stick with READ COMMITTED, or use snapshot isolation. NOLOCK is only remotely useful for queries where the correct results don't really matter (for example, monitoring queries that will be re-run momentarily).
– Jeroen Mostert
Nov 19 at 16:06

4

Yes, nolock can skip rows. See dba.stackexchange.com/a/71640/5203 and the links from there.
– GSerg
Nov 19 at 16:06

2

NOLOCK performs a "raw" read on the page structures. It is not only not consistent on the row level, it's not consistent at all. It is absolutely possible for it to skip all rows you're interested in, if it comes in in the middle of a page split. Source: personal experience from a query that did a bunch of COUNT(*)s with a GROUP BY that suddenly started reporting that my table was completely empty when the server was under heavy load (when, obviously, it was not). This issue only occurred once, but it was serious enough that it justified a switch to snapshot isolation.
– Jeroen Mostert
Nov 19 at 16:15

1

Your query is syntactically incorrect - perhaps caused by your obfuscation attempts? You cannot select 3 columns and group by just one of those.
– SMor
Nov 19 at 16:41

|
show 5 more comments

up vote
-1
down vote

favorite

I am sure that the data is still there and that there shouldn't be anything that would delete it...

I had a look in a few places and there's no way I could find what might be causing the issue mentioned above ... also, there doesn't seem to be an easy way to replicate it... it's just random.

As my SQL knowledge is quite limited, could you please think of certain scenarios where that might occur? Or maybe suggest easy ways of debugging this?

SELECT MAX(Table1.Cust_ID) AS cust_id, Table1.Email, MAX(Table1.Cust_Name) as cust_name

FROM dbo.Table1 WITH (NOLOCK)

INNER JOIN dbo.Table2 WITH (NOLOCK)

  ON Table1.id = Table2.id

  WHERE Table1.some_id = 100 AND Table1.some_value = 'test_value'

  AND  Table1.some_boolean_1 = 1

  AND  Table1.some_date_1 IS NULL

  AND (Table1.some_boolean_2 IS NULL OR Table1.some_boolean_2 = 1)

  AND (Table1.some_date_2 > '20171119' OR Table1.some_date_3 > '20171119' OR 

       Table2.some_date_3 > '20171119')

GROUP BY Table1.Email

Many thanks!

edited Nov 19 at 16:54

asked Nov 19 at 16:01

Catalin Lupuleti

178

I am sure that the data is still there and that there shouldn't be anything that would delete it...

I had a look in a few places and there's no way I could find what might be causing the issue mentioned above ... also, there doesn't seem to be an easy way to replicate it... it's just random.

As my SQL knowledge is quite limited, could you please think of certain scenarios where that might occur? Or maybe suggest easy ways of debugging this?

SELECT MAX(Table1.Cust_ID) AS cust_id, Table1.Email, MAX(Table1.Cust_Name) as cust_name

FROM dbo.Table1 WITH (NOLOCK)

INNER JOIN dbo.Table2 WITH (NOLOCK)

  ON Table1.id = Table2.id

  WHERE Table1.some_id = 100 AND Table1.some_value = 'test_value'

  AND  Table1.some_boolean_1 = 1

  AND  Table1.some_date_1 IS NULL

  AND (Table1.some_boolean_2 IS NULL OR Table1.some_boolean_2 = 1)

  AND (Table1.some_date_2 > '20171119' OR Table1.some_date_3 > '20171119' OR 

       Table2.some_date_3 > '20171119')

GROUP BY Table1.Email

Many thanks!

sql sql-server nolock

edited Nov 19 at 16:54

asked Nov 19 at 16:01

Catalin Lupuleti

178

edited Nov 19 at 16:54

asked Nov 19 at 16:01

Catalin Lupuleti

178

edited Nov 19 at 16:54

asked Nov 19 at 16:01

Catalin Lupuleti

178

asked Nov 19 at 16:01

Catalin Lupuleti

178

asked Nov 19 at 16:01

Catalin Lupuleti

178

Go line by line and check all those WHERE clauses and find where the records fall off? Query the tables every few seconds and confirm records are not being mass deleted?
– dfundako
Nov 19 at 16:04

9

This may well be "working as designed". NOLOCK can skip rows, repeat rows and (in older versions, at least) error out as a result of data movement, especially under load. If you need reliable results, don't use NOLOCK -- stick with READ COMMITTED, or use snapshot isolation. NOLOCK is only remotely useful for queries where the correct results don't really matter (for example, monitoring queries that will be re-run momentarily).
– Jeroen Mostert
Nov 19 at 16:06

4

Yes, nolock can skip rows. See dba.stackexchange.com/a/71640/5203 and the links from there.
– GSerg
Nov 19 at 16:06

2

NOLOCK performs a "raw" read on the page structures. It is not only not consistent on the row level, it's not consistent at all. It is absolutely possible for it to skip all rows you're interested in, if it comes in in the middle of a page split. Source: personal experience from a query that did a bunch of COUNT(*)s with a GROUP BY that suddenly started reporting that my table was completely empty when the server was under heavy load (when, obviously, it was not). This issue only occurred once, but it was serious enough that it justified a switch to snapshot isolation.
– Jeroen Mostert
Nov 19 at 16:15

1

Your query is syntactically incorrect - perhaps caused by your obfuscation attempts? You cannot select 3 columns and group by just one of those.
– SMor
Nov 19 at 16:41

|
show 5 more comments

Go line by line and check all those WHERE clauses and find where the records fall off? Query the tables every few seconds and confirm records are not being mass deleted?
– dfundako
Nov 19 at 16:04

9

This may well be "working as designed". NOLOCK can skip rows, repeat rows and (in older versions, at least) error out as a result of data movement, especially under load. If you need reliable results, don't use NOLOCK -- stick with READ COMMITTED, or use snapshot isolation. NOLOCK is only remotely useful for queries where the correct results don't really matter (for example, monitoring queries that will be re-run momentarily).
– Jeroen Mostert
Nov 19 at 16:06

4

Yes, nolock can skip rows. See dba.stackexchange.com/a/71640/5203 and the links from there.
– GSerg
Nov 19 at 16:06

2

NOLOCK performs a "raw" read on the page structures. It is not only not consistent on the row level, it's not consistent at all. It is absolutely possible for it to skip all rows you're interested in, if it comes in in the middle of a page split. Source: personal experience from a query that did a bunch of COUNT(*)s with a GROUP BY that suddenly started reporting that my table was completely empty when the server was under heavy load (when, obviously, it was not). This issue only occurred once, but it was serious enough that it justified a switch to snapshot isolation.
– Jeroen Mostert
Nov 19 at 16:15

1

Your query is syntactically incorrect - perhaps caused by your obfuscation attempts? You cannot select 3 columns and group by just one of those.
– SMor
Nov 19 at 16:41

Go line by line and check all those WHERE clauses and find where the records fall off? Query the tables every few seconds and confirm records are not being mass deleted?
– dfundako
Nov 19 at 16:04

This may well be "working as designed". NOLOCK can skip rows, repeat rows and (in older versions, at least) error out as a result of data movement, especially under load. If you need reliable results, don't use NOLOCK -- stick with READ COMMITTED, or use snapshot isolation. NOLOCK is only remotely useful for queries where the correct results don't really matter (for example, monitoring queries that will be re-run momentarily).
– Jeroen Mostert
Nov 19 at 16:06

Yes, nolock can skip rows. See dba.stackexchange.com/a/71640/5203 and the links from there.
– GSerg
Nov 19 at 16:06

NOLOCK performs a "raw" read on the page structures. It is not only not consistent on the row level, it's not consistent at all. It is absolutely possible for it to skip all rows you're interested in, if it comes in in the middle of a page split. Source: personal experience from a query that did a bunch of COUNT(*)s with a GROUP BY that suddenly started reporting that my table was completely empty when the server was under heavy load (when, obviously, it was not). This issue only occurred once, but it was serious enough that it justified a switch to snapshot isolation.
– Jeroen Mostert
Nov 19 at 16:15

Your query is syntactically incorrect - perhaps caused by your obfuscation attempts? You cannot select 3 columns and group by just one of those.
– SMor
Nov 19 at 16:41

|
show 5 more comments

1 Answer
1

active

oldest

votes

up vote
-2
down vote

The WITH(NOLOCK) hint makes SQL Server execute the query in a non-transactional way, meaning data integrity is ignored, and results can be any kinds of intermediate state. Same effect as setting the transaction isolation level to READ UNCOMMITTED, but for this query only. Results can include also inserted, but not yet committed rows, and anything running besides a transaction. Not sure what causes the empty result, but it can return strange results when modifications run at the same time.

edited Nov 19 at 16:18

answered Nov 19 at 16:11

Erik Hart

567518

1

@Downvoters: please specify what's wrong!
– Erik Hart
Nov 19 at 16:38

2

I didn't downvote your answer, but the opening line, The WITH(NOLOCK) hint makes SQL Server execute the query in a non-transactional way is at best misleading and at worst incorrect. WITH(NOLOCK) ignores the state of other open transactions on the underlying table, but that's hardly "non-transactional".
– Eric Brandt
Nov 19 at 16:44

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53378454%2fselect-with-nolock-sometimes-returns-no-results%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
-2
down vote

edited Nov 19 at 16:18

answered Nov 19 at 16:11

Erik Hart

567518

1

@Downvoters: please specify what's wrong!
– Erik Hart
Nov 19 at 16:38

2

I didn't downvote your answer, but the opening line, The WITH(NOLOCK) hint makes SQL Server execute the query in a non-transactional way is at best misleading and at worst incorrect. WITH(NOLOCK) ignores the state of other open transactions on the underlying table, but that's hardly "non-transactional".
– Eric Brandt
Nov 19 at 16:44

add a comment |

up vote
-2
down vote

edited Nov 19 at 16:18

answered Nov 19 at 16:11

Erik Hart

567518

1

@Downvoters: please specify what's wrong!
– Erik Hart
Nov 19 at 16:38

2

I didn't downvote your answer, but the opening line, The WITH(NOLOCK) hint makes SQL Server execute the query in a non-transactional way is at best misleading and at worst incorrect. WITH(NOLOCK) ignores the state of other open transactions on the underlying table, but that's hardly "non-transactional".
– Eric Brandt
Nov 19 at 16:44

add a comment |

up vote
-2
down vote

edited Nov 19 at 16:18

answered Nov 19 at 16:11

Erik Hart

567518

edited Nov 19 at 16:18

answered Nov 19 at 16:11

Erik Hart

567518

edited Nov 19 at 16:18

answered Nov 19 at 16:11

Erik Hart

567518

answered Nov 19 at 16:11

Erik Hart

567518

answered Nov 19 at 16:11

Erik Hart

567518

1

@Downvoters: please specify what's wrong!
– Erik Hart
Nov 19 at 16:38

2

I didn't downvote your answer, but the opening line, The WITH(NOLOCK) hint makes SQL Server execute the query in a non-transactional way is at best misleading and at worst incorrect. WITH(NOLOCK) ignores the state of other open transactions on the underlying table, but that's hardly "non-transactional".
– Eric Brandt
Nov 19 at 16:44

add a comment |

1

@Downvoters: please specify what's wrong!
– Erik Hart
Nov 19 at 16:38

2

I didn't downvote your answer, but the opening line, The WITH(NOLOCK) hint makes SQL Server execute the query in a non-transactional way is at best misleading and at worst incorrect. WITH(NOLOCK) ignores the state of other open transactions on the underlying table, but that's hardly "non-transactional".
– Eric Brandt
Nov 19 at 16:44

@Downvoters: please specify what's wrong!
– Erik Hart
Nov 19 at 16:38

I didn't downvote your answer, but the opening line, The WITH(NOLOCK) hint makes SQL Server execute the query in a non-transactional way is at best misleading and at worst incorrect. WITH(NOLOCK) ignores the state of other open transactions on the underlying table, but that's hardly "non-transactional".
– Eric Brandt
Nov 19 at 16:44

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Tukukkk