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!
sql
sql-server nolock asked Nov 19 at 16:01
Catalin Lupuleti
178
|
show 5 more comments
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!
sql
sql-server nolock 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".NOLOCKcan 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 useNOLOCK-- stick withREAD COMMITTED, or use snapshot isolation.NOLOCKis 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,nolockcan skip rows. See dba.stackexchange.com/a/71640/5203 and the links from there.
– GSerg
Nov 19 at 16:06
2
NOLOCKperforms 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 ofCOUNT(*)s with aGROUP BYthat 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
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!
sql
sql-server nolock asked Nov 19 at 16:01
Catalin Lupuleti
178
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!
sql
sql-server nolock sql
sql-server nolock asked Nov 19 at 16:01
Catalin Lupuleti
178
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
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".NOLOCKcan 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 useNOLOCK-- stick withREAD COMMITTED, or use snapshot isolation.NOLOCKis 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,nolockcan skip rows. See dba.stackexchange.com/a/71640/5203 and the links from there.
– GSerg
Nov 19 at 16:06
2
NOLOCKperforms 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 ofCOUNT(*)s with aGROUP BYthat 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".NOLOCKcan 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 useNOLOCK-- stick withREAD COMMITTED, or use snapshot isolation.NOLOCKis 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,nolockcan skip rows. See dba.stackexchange.com/a/71640/5203 and the links from there.
– GSerg
Nov 19 at 16:06
2
NOLOCKperforms 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 ofCOUNT(*)s with aGROUP BYthat 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
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
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
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
4
Yes,
nolock can skip rows. See dba.stackexchange.com/a/71640/5203 and the links from there.– GSerg
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
2
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
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
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
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.
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 wayis 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 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
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.
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 wayis 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
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.
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 wayis 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
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.
answered Nov 19 at 16:11
Erik Hart
567518
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.
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
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 wayis 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 wayis 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
1
1
@Downvoters: please specify what's wrong!
– Erik Hart
Nov 19 at 16:38
@Downvoters: please specify what's wrong!
– Erik Hart
Nov 19 at 16:38
2
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
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 |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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".
NOLOCKcan 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 useNOLOCK-- stick withREAD COMMITTED, or use snapshot isolation.NOLOCKis 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,
nolockcan skip rows. See dba.stackexchange.com/a/71640/5203 and the links from there.– GSerg
Nov 19 at 16:06
2
NOLOCKperforms 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 ofCOUNT(*)s with aGROUP BYthat 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