Understanding SQL Server LOCKS on SELECT queries
I'm wondering what is the benefit to use SELECT WITH (NOLOCK) on a table if the only other queries affecting that table are SELECT queries.
How is that handled by SQL Server? Would a SELECT query block another SELECT query?
I'm using SQL Server 2012 and a Linq-to-SQL DataContext.
(EDIT)
About performance :
- Would a 2nd
SELECThave to wait for a 1stSELECTto finish if using a lockedSELECT? - Versus a
SELECT WITH (NOLOCK)?
sql-server tsql linq-to-sql edited Nov 23 '18 at 21:55
Benjamin
15.9k29122231
asked Sep 26 '12 at 19:12
Francis PFrancis P
8,93021846
add a comment |
I'm wondering what is the benefit to use SELECT WITH (NOLOCK) on a table if the only other queries affecting that table are SELECT queries.
How is that handled by SQL Server? Would a SELECT query block another SELECT query?
I'm using SQL Server 2012 and a Linq-to-SQL DataContext.
(EDIT)
About performance :
- Would a 2nd
SELECThave to wait for a 1stSELECTto finish if using a lockedSELECT? - Versus a
SELECT WITH (NOLOCK)?
sql-server tsql linq-to-sql edited Nov 23 '18 at 21:55
Benjamin
15.9k29122231
asked Sep 26 '12 at 19:12
Francis PFrancis P
8,93021846
add a comment |
I'm wondering what is the benefit to use SELECT WITH (NOLOCK) on a table if the only other queries affecting that table are SELECT queries.
How is that handled by SQL Server? Would a SELECT query block another SELECT query?
I'm using SQL Server 2012 and a Linq-to-SQL DataContext.
(EDIT)
About performance :
- Would a 2nd
SELECThave to wait for a 1stSELECTto finish if using a lockedSELECT? - Versus a
SELECT WITH (NOLOCK)?
sql-server tsql linq-to-sql edited Nov 23 '18 at 21:55
Benjamin
15.9k29122231
asked Sep 26 '12 at 19:12
Francis PFrancis P
8,93021846
I'm wondering what is the benefit to use SELECT WITH (NOLOCK) on a table if the only other queries affecting that table are SELECT queries.
How is that handled by SQL Server? Would a SELECT query block another SELECT query?
I'm using SQL Server 2012 and a Linq-to-SQL DataContext.
(EDIT)
About performance :
- Would a 2nd
SELECThave to wait for a 1stSELECTto finish if using a lockedSELECT? - Versus a
SELECT WITH (NOLOCK)?
sql-server tsql linq-to-sql sql-server tsql linq-to-sql edited Nov 23 '18 at 21:55
Benjamin
15.9k29122231
asked Sep 26 '12 at 19:12
Francis PFrancis P
8,93021846
edited Nov 23 '18 at 21:55
Benjamin
15.9k29122231
asked Sep 26 '12 at 19:12
Francis PFrancis P
8,93021846
edited Nov 23 '18 at 21:55
Benjamin
15.9k29122231
edited Nov 23 '18 at 21:55
Benjamin
15.9k29122231
edited Nov 23 '18 at 21:55
Benjamin
15.9k29122231
15.9k29122231
asked Sep 26 '12 at 19:12
Francis PFrancis P
8,93021846
asked Sep 26 '12 at 19:12
Francis PFrancis P
8,93021846
asked Sep 26 '12 at 19:12
Francis PFrancis P
8,93021846
8,93021846
add a comment |
add a comment |
6 Answers
6
active
oldest
votes
A SELECT in SQL Server will place a shared lock on a table row - and a second SELECT would also require a shared lock, and those are compatible with one another.
So no - one SELECT cannot block another SELECT.
What the WITH (NOLOCK) query hint is used for is to be able to read data that's in the process of being inserted (by another connection) and that hasn't been committed yet.
Without that query hint, a SELECT might be blocked reading a table by an ongoing INSERT (or UPDATE) statement that places an exclusive lock on rows (or possibly a whole table), until that operation's transaction has been committed (or rolled back).
Problem of the WITH (NOLOCK) hint is: you might be reading data rows that aren't going to be inserted at all, in the end (if the INSERT transaction is rolled back) - so your e.g. report might show data that's never really been committed to the database.
There's another query hint that might be useful - WITH (READPAST). This instructs the SELECT command to just skip any rows that it attempts to read and that are locked exclusively. The SELECT will not block, and it will not read any "dirty" un-committed data - but it might skip some rows, e.g. not show all your rows in the table.
answered Sep 26 '12 at 21:02
marc_smarc_s
576k12911111258
1
Nice answer, thanks a lot! Would there be an impact (on hundreds ofSELECTqueries) to useWITH (NOLOCK)without reason?
– Francis P
Sep 26 '12 at 21:28
1
We use with nolock in 99.5% of our selects, no joke. If an admin is updating a user record you don't want that to cause the report to sit there and wait for the whole distributed transaction to finish. So their old data shows up on the report. Who cares? If the report had been run a second before that's the same data that would have been there with rowlock. The only place it's a concern is data that isn't committed yet. If you're showing "orders in the last hour" that could potentially be an issue, but a tiny, tiny issue compared to speed/concurrency gains.
– Brian White
Sep 27 '12 at 1:57
3
Also since 'report' was thrown out as an example, reports are typically for a time period that is not the past 5 minutes. Reporting on data from last month with nolock - well it's not like the data is going to rollback a month later.
– Brian White
Sep 27 '12 at 2:06
2
@FrancisP: not if you insert a small number of rows - in that case, it just locks the new rows being inserted. If you insert more than roughly 5000 rows at once - then a lock escalation will occur and the whole table will be exclusively locked.
– marc_s
Sep 27 '12 at 14:06
1
Very nice answer..It felt like an all in one tutorial for SQL locks!! glad I got in here!
– digitally_inspired
Nov 16 '18 at 15:47
|
show 4 more comments
On performance you keep focusing on select.
Shared does not block reads.
Shared lock blocks update.
If you have hundreds of shared locks it is going to take an update a while to get an exclusive lock as it must wait for shared locks to clear.
By default a select (read) takes a shared lock.
Shared (S) locks allow concurrent transactions to read (SELECT) a resource.
A shared lock as no effect on other selects (1 or a 1000).
The difference is how the nolock versus shared lock effects update or insert operation.
No other transactions can modify the data while shared (S) locks exist on the resource.
A shared lock blocks an update!
But nolock does not block an update.
This can have huge impacts on performance of updates. It also impact inserts.
Dirty read (nolock) just sounds dirty. You are never going to get partial data. If an update is changing John to Sally you are never going to get Jolly.
I use shared locks a lot for concurrency. Data is stale as soon as it is read. A read of John that changes to Sally the next millisecond is stale data. A read of Sally that gets rolled back John the next millisecond is stale data. That is on the millisecond level. I have a dataloader that take 20 hours to run if users are taking shared locks and 4 hours to run is users are taking no lock. Shared locks in this case cause data to be 16 hours stale.
Don't use nolocks wrong. But they do have a place. If you are going to cut a check when a byte is set to 1 and then set it to 2 when the check is cut - not a time for a nolock.
answered Sep 26 '12 at 22:31
paparazzopaparazzo
37.6k1675138
1
Thank you. We see similar performance characteristics. Our site wouldn't run if we required locks for reads, and the impact of not having it in most cases is just insignificant.
– Brian White
Sep 27 '12 at 2:00
@BrianWhite Thank you. Someone gets it. And I take a lot of table locks on update and insert. Get in, get it done, and get out is my approach.
– paparazzo
Sep 27 '12 at 2:21
1
Dirty read (nolock) just sounds dirty. You are never going to get partial data. If an update is changing John to Sally you are never going to get Jolly. - we read John right?
– MonsterMMORPG
Jul 18 '16 at 12:54
1
Updates in sql server use update lock (U) that is later converted to exclusive lock (X). (see madeiradata.com/role-update-lock-sql-server) Update lock doesn't block shared locks, but exclusive lock blocks all other locks (see msdn.microsoft.com/en-us/library/ms186396(v=sql.105).aspx).
– kolobok
Feb 21 '18 at 15:56
@kolobok going to take an update a while to get an exclusive lock
– paparazzo
Feb 21 '18 at 16:13
add a comment |
At my work, we have a very big system that runs on many PCs at the same time, with very big tables with hundreds of thousands of rows, and sometimes many millions of rows.
When you make a SELECT on a very big table, let's say you want to know every transaction a user has made in the past 10 years, and the primary key of the table is not built in an efficient way, the query might take several minutes to run.
Then, our application might me running on many user's PCs at the same time, accessing the same database. So if someone tries to insert into the table that the other SELECT is reading (in pages that SQL is trying to read), then a LOCK can occur and the two transactions block each other.
We had to add a "NO LOCK" to our SELECT statement, because it was a huge SELECT on a table that is used a lot by a lot of users at the same time and we had LOCKS all the time.
I don't know if my example is clear enough? This is a real life example.
edited Apr 21 '15 at 10:29
JonnyRaa
4,65213236
answered Sep 26 '12 at 19:34
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,023922
Thank you for the example, but I'm only wondering about SELECT queries affecting other SELECT queries (on that same table)..
– Francis P
Sep 26 '12 at 19:36
1
They won't, but a select statement could be part of transaction that includes an update. Update tbl set x = (select max(y) from tbl) where z = (select min(a) from tbl). If you have a concurrent select z from tbl well the other selects aren't blocking it, but the update is.
– Brian White
Sep 27 '12 at 2:03
1
I had exactly this issue that a long running select was blocking my inserts
– nojetlag
Jul 3 '13 at 14:13
2
The transactions won't block each other - the select will block the update. Here's a couple of interesting links that have just helped me understand a little more about how this stuff works: first one second one
– JonnyRaa
Apr 21 '15 at 9:54
@JonnyLeeds : Your 2nd link doesn't work anymore. Here is an archived link of SQL Server: Locking basics
– stomy
Apr 19 '18 at 15:36
add a comment |
I have to add an important comment. Everyone is mentioning that NOLOCKreads only dirty data. This is not precise. It is also possible that you'll get same row twice or whole row is skipped during your read. Reason is that you could ask for some data in same time when SQL Server is re-balancing b-tree.
Check another threads
https://stackoverflow.com/a/5469238/2108874
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii.aspx)
With the NOLOCK hint (or setting the isolation level of the session to READ UNCOMMITTED) you tell SQL Server that you don't expect consistency, so there are no guarantees. Bear in mind though that "inconsistent data" does not only mean that you might see uncommitted changes that were later rolled back, or data changes in an intermediate state of the transaction. It also means that in a simple query that scans all table/index data SQL Server may lose the scan position, or you might end up getting the same row twice.
edited May 23 '17 at 11:47
Community♦
11
answered Mar 4 '15 at 8:59
Milan MatějkaMilan Matějka
2,0441319
add a comment |
The SELECT WITH (NOLOCK) allows reads of uncommitted data, which is equivalent to having the READ UNCOMMITTED isolation level set on your database. The NOLOCK keyword allows finer grained control than setting the isolation level on the entire database.
Wikipedia has a useful article: Wikipedia: Isolation (database systems)
It is also discussed at length in other stackoverflow articles.
answered Dec 5 '14 at 8:42
rghomerghome
4,81462641
Thanks rghome for the additional information you provided.
– Francis P
Dec 5 '14 at 16:03
This is why I prefer using theREADUNCOMMITTED(an alias forNOLOCK) hint, when such is a valid use-case. Doing so makes the actual operation, which isn't really "without locks", less unclear.
– user2864740
Dec 16 '15 at 22:43
add a comment |
select with no lock - will select records which may / may not going to be inserted. you will read a dirty data.
for example - lets say a transaction insert 1000 rows and then fails.
when you select - you will get the 1000 rows.
answered Sep 26 '12 at 19:15
Royi NamirRoyi Namir
75.5k98332591
But what if no record is intended to be inserted in that table, is the NO LOCK still relevant?
– Francis P
Sep 26 '12 at 19:21
no it is not. since read uses a shared lock which can be acquired by more than 1 sessions. there is no way of getting dirty data.
– Royi Namir
Sep 26 '12 at 19:23
And on a performance POV?
– Francis P
Sep 26 '12 at 19:28
I rather not to answer on a thing I'm not sure of. :-)
– Royi Namir
Sep 26 '12 at 19:29
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',
autoActivateHeartbeat: false,
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
});
}
});
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%2f12608780%2funderstanding-sql-server-locks-on-select-queries%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
6 Answers
6
active
oldest
votes
6 Answers
6
active
oldest
votes
active
oldest
votes
active
oldest
votes
A SELECT in SQL Server will place a shared lock on a table row - and a second SELECT would also require a shared lock, and those are compatible with one another.
So no - one SELECT cannot block another SELECT.
What the WITH (NOLOCK) query hint is used for is to be able to read data that's in the process of being inserted (by another connection) and that hasn't been committed yet.
Without that query hint, a SELECT might be blocked reading a table by an ongoing INSERT (or UPDATE) statement that places an exclusive lock on rows (or possibly a whole table), until that operation's transaction has been committed (or rolled back).
Problem of the WITH (NOLOCK) hint is: you might be reading data rows that aren't going to be inserted at all, in the end (if the INSERT transaction is rolled back) - so your e.g. report might show data that's never really been committed to the database.
There's another query hint that might be useful - WITH (READPAST). This instructs the SELECT command to just skip any rows that it attempts to read and that are locked exclusively. The SELECT will not block, and it will not read any "dirty" un-committed data - but it might skip some rows, e.g. not show all your rows in the table.
answered Sep 26 '12 at 21:02
marc_smarc_s
576k12911111258
1
Nice answer, thanks a lot! Would there be an impact (on hundreds ofSELECTqueries) to useWITH (NOLOCK)without reason?
– Francis P
Sep 26 '12 at 21:28
1
We use with nolock in 99.5% of our selects, no joke. If an admin is updating a user record you don't want that to cause the report to sit there and wait for the whole distributed transaction to finish. So their old data shows up on the report. Who cares? If the report had been run a second before that's the same data that would have been there with rowlock. The only place it's a concern is data that isn't committed yet. If you're showing "orders in the last hour" that could potentially be an issue, but a tiny, tiny issue compared to speed/concurrency gains.
– Brian White
Sep 27 '12 at 1:57
3
Also since 'report' was thrown out as an example, reports are typically for a time period that is not the past 5 minutes. Reporting on data from last month with nolock - well it's not like the data is going to rollback a month later.
– Brian White
Sep 27 '12 at 2:06
2
@FrancisP: not if you insert a small number of rows - in that case, it just locks the new rows being inserted. If you insert more than roughly 5000 rows at once - then a lock escalation will occur and the whole table will be exclusively locked.
– marc_s
Sep 27 '12 at 14:06
1
Very nice answer..It felt like an all in one tutorial for SQL locks!! glad I got in here!
– digitally_inspired
Nov 16 '18 at 15:47
|
show 4 more comments
A SELECT in SQL Server will place a shared lock on a table row - and a second SELECT would also require a shared lock, and those are compatible with one another.
So no - one SELECT cannot block another SELECT.
What the WITH (NOLOCK) query hint is used for is to be able to read data that's in the process of being inserted (by another connection) and that hasn't been committed yet.
Without that query hint, a SELECT might be blocked reading a table by an ongoing INSERT (or UPDATE) statement that places an exclusive lock on rows (or possibly a whole table), until that operation's transaction has been committed (or rolled back).
Problem of the WITH (NOLOCK) hint is: you might be reading data rows that aren't going to be inserted at all, in the end (if the INSERT transaction is rolled back) - so your e.g. report might show data that's never really been committed to the database.
There's another query hint that might be useful - WITH (READPAST). This instructs the SELECT command to just skip any rows that it attempts to read and that are locked exclusively. The SELECT will not block, and it will not read any "dirty" un-committed data - but it might skip some rows, e.g. not show all your rows in the table.
answered Sep 26 '12 at 21:02
marc_smarc_s
576k12911111258
1
Nice answer, thanks a lot! Would there be an impact (on hundreds ofSELECTqueries) to useWITH (NOLOCK)without reason?
– Francis P
Sep 26 '12 at 21:28
1
We use with nolock in 99.5% of our selects, no joke. If an admin is updating a user record you don't want that to cause the report to sit there and wait for the whole distributed transaction to finish. So their old data shows up on the report. Who cares? If the report had been run a second before that's the same data that would have been there with rowlock. The only place it's a concern is data that isn't committed yet. If you're showing "orders in the last hour" that could potentially be an issue, but a tiny, tiny issue compared to speed/concurrency gains.
– Brian White
Sep 27 '12 at 1:57
3
Also since 'report' was thrown out as an example, reports are typically for a time period that is not the past 5 minutes. Reporting on data from last month with nolock - well it's not like the data is going to rollback a month later.
– Brian White
Sep 27 '12 at 2:06
2
@FrancisP: not if you insert a small number of rows - in that case, it just locks the new rows being inserted. If you insert more than roughly 5000 rows at once - then a lock escalation will occur and the whole table will be exclusively locked.
– marc_s
Sep 27 '12 at 14:06
1
Very nice answer..It felt like an all in one tutorial for SQL locks!! glad I got in here!
– digitally_inspired
Nov 16 '18 at 15:47
|
show 4 more comments
A SELECT in SQL Server will place a shared lock on a table row - and a second SELECT would also require a shared lock, and those are compatible with one another.
So no - one SELECT cannot block another SELECT.
What the WITH (NOLOCK) query hint is used for is to be able to read data that's in the process of being inserted (by another connection) and that hasn't been committed yet.
Without that query hint, a SELECT might be blocked reading a table by an ongoing INSERT (or UPDATE) statement that places an exclusive lock on rows (or possibly a whole table), until that operation's transaction has been committed (or rolled back).
Problem of the WITH (NOLOCK) hint is: you might be reading data rows that aren't going to be inserted at all, in the end (if the INSERT transaction is rolled back) - so your e.g. report might show data that's never really been committed to the database.
There's another query hint that might be useful - WITH (READPAST). This instructs the SELECT command to just skip any rows that it attempts to read and that are locked exclusively. The SELECT will not block, and it will not read any "dirty" un-committed data - but it might skip some rows, e.g. not show all your rows in the table.
answered Sep 26 '12 at 21:02
marc_smarc_s
576k12911111258
A SELECT in SQL Server will place a shared lock on a table row - and a second SELECT would also require a shared lock, and those are compatible with one another.
So no - one SELECT cannot block another SELECT.
What the WITH (NOLOCK) query hint is used for is to be able to read data that's in the process of being inserted (by another connection) and that hasn't been committed yet.
Without that query hint, a SELECT might be blocked reading a table by an ongoing INSERT (or UPDATE) statement that places an exclusive lock on rows (or possibly a whole table), until that operation's transaction has been committed (or rolled back).
Problem of the WITH (NOLOCK) hint is: you might be reading data rows that aren't going to be inserted at all, in the end (if the INSERT transaction is rolled back) - so your e.g. report might show data that's never really been committed to the database.
There's another query hint that might be useful - WITH (READPAST). This instructs the SELECT command to just skip any rows that it attempts to read and that are locked exclusively. The SELECT will not block, and it will not read any "dirty" un-committed data - but it might skip some rows, e.g. not show all your rows in the table.
answered Sep 26 '12 at 21:02
marc_smarc_s
576k12911111258
answered Sep 26 '12 at 21:02
marc_smarc_s
576k12911111258
answered Sep 26 '12 at 21:02
marc_smarc_s
576k12911111258
answered Sep 26 '12 at 21:02
marc_smarc_s
576k12911111258
576k12911111258
1
Nice answer, thanks a lot! Would there be an impact (on hundreds ofSELECTqueries) to useWITH (NOLOCK)without reason?
– Francis P
Sep 26 '12 at 21:28
1
We use with nolock in 99.5% of our selects, no joke. If an admin is updating a user record you don't want that to cause the report to sit there and wait for the whole distributed transaction to finish. So their old data shows up on the report. Who cares? If the report had been run a second before that's the same data that would have been there with rowlock. The only place it's a concern is data that isn't committed yet. If you're showing "orders in the last hour" that could potentially be an issue, but a tiny, tiny issue compared to speed/concurrency gains.
– Brian White
Sep 27 '12 at 1:57
3
Also since 'report' was thrown out as an example, reports are typically for a time period that is not the past 5 minutes. Reporting on data from last month with nolock - well it's not like the data is going to rollback a month later.
– Brian White
Sep 27 '12 at 2:06
2
@FrancisP: not if you insert a small number of rows - in that case, it just locks the new rows being inserted. If you insert more than roughly 5000 rows at once - then a lock escalation will occur and the whole table will be exclusively locked.
– marc_s
Sep 27 '12 at 14:06
1
Very nice answer..It felt like an all in one tutorial for SQL locks!! glad I got in here!
– digitally_inspired
Nov 16 '18 at 15:47
|
show 4 more comments
1
Nice answer, thanks a lot! Would there be an impact (on hundreds ofSELECTqueries) to useWITH (NOLOCK)without reason?
– Francis P
Sep 26 '12 at 21:28
1
We use with nolock in 99.5% of our selects, no joke. If an admin is updating a user record you don't want that to cause the report to sit there and wait for the whole distributed transaction to finish. So their old data shows up on the report. Who cares? If the report had been run a second before that's the same data that would have been there with rowlock. The only place it's a concern is data that isn't committed yet. If you're showing "orders in the last hour" that could potentially be an issue, but a tiny, tiny issue compared to speed/concurrency gains.
– Brian White
Sep 27 '12 at 1:57
3
Also since 'report' was thrown out as an example, reports are typically for a time period that is not the past 5 minutes. Reporting on data from last month with nolock - well it's not like the data is going to rollback a month later.
– Brian White
Sep 27 '12 at 2:06
2
@FrancisP: not if you insert a small number of rows - in that case, it just locks the new rows being inserted. If you insert more than roughly 5000 rows at once - then a lock escalation will occur and the whole table will be exclusively locked.
– marc_s
Sep 27 '12 at 14:06
1
Very nice answer..It felt like an all in one tutorial for SQL locks!! glad I got in here!
– digitally_inspired
Nov 16 '18 at 15:47
1
1
Nice answer, thanks a lot! Would there be an impact (on hundreds of
SELECT queries) to use WITH (NOLOCK) without reason?– Francis P
Sep 26 '12 at 21:28
Nice answer, thanks a lot! Would there be an impact (on hundreds of
SELECT queries) to use WITH (NOLOCK) without reason?– Francis P
Sep 26 '12 at 21:28
1
1
We use with nolock in 99.5% of our selects, no joke. If an admin is updating a user record you don't want that to cause the report to sit there and wait for the whole distributed transaction to finish. So their old data shows up on the report. Who cares? If the report had been run a second before that's the same data that would have been there with rowlock. The only place it's a concern is data that isn't committed yet. If you're showing "orders in the last hour" that could potentially be an issue, but a tiny, tiny issue compared to speed/concurrency gains.
– Brian White
Sep 27 '12 at 1:57
We use with nolock in 99.5% of our selects, no joke. If an admin is updating a user record you don't want that to cause the report to sit there and wait for the whole distributed transaction to finish. So their old data shows up on the report. Who cares? If the report had been run a second before that's the same data that would have been there with rowlock. The only place it's a concern is data that isn't committed yet. If you're showing "orders in the last hour" that could potentially be an issue, but a tiny, tiny issue compared to speed/concurrency gains.
– Brian White
Sep 27 '12 at 1:57
3
3
Also since 'report' was thrown out as an example, reports are typically for a time period that is not the past 5 minutes. Reporting on data from last month with nolock - well it's not like the data is going to rollback a month later.
– Brian White
Sep 27 '12 at 2:06
Also since 'report' was thrown out as an example, reports are typically for a time period that is not the past 5 minutes. Reporting on data from last month with nolock - well it's not like the data is going to rollback a month later.
– Brian White
Sep 27 '12 at 2:06
2
2
@FrancisP: not if you insert a small number of rows - in that case, it just locks the new rows being inserted. If you insert more than roughly 5000 rows at once - then a lock escalation will occur and the whole table will be exclusively locked.
– marc_s
Sep 27 '12 at 14:06
@FrancisP: not if you insert a small number of rows - in that case, it just locks the new rows being inserted. If you insert more than roughly 5000 rows at once - then a lock escalation will occur and the whole table will be exclusively locked.
– marc_s
Sep 27 '12 at 14:06
1
1
Very nice answer..It felt like an all in one tutorial for SQL locks!! glad I got in here!
– digitally_inspired
Nov 16 '18 at 15:47
Very nice answer..It felt like an all in one tutorial for SQL locks!! glad I got in here!
– digitally_inspired
Nov 16 '18 at 15:47
|
show 4 more comments
On performance you keep focusing on select.
Shared does not block reads.
Shared lock blocks update.
If you have hundreds of shared locks it is going to take an update a while to get an exclusive lock as it must wait for shared locks to clear.
By default a select (read) takes a shared lock.
Shared (S) locks allow concurrent transactions to read (SELECT) a resource.
A shared lock as no effect on other selects (1 or a 1000).
The difference is how the nolock versus shared lock effects update or insert operation.
No other transactions can modify the data while shared (S) locks exist on the resource.
A shared lock blocks an update!
But nolock does not block an update.
This can have huge impacts on performance of updates. It also impact inserts.
Dirty read (nolock) just sounds dirty. You are never going to get partial data. If an update is changing John to Sally you are never going to get Jolly.
I use shared locks a lot for concurrency. Data is stale as soon as it is read. A read of John that changes to Sally the next millisecond is stale data. A read of Sally that gets rolled back John the next millisecond is stale data. That is on the millisecond level. I have a dataloader that take 20 hours to run if users are taking shared locks and 4 hours to run is users are taking no lock. Shared locks in this case cause data to be 16 hours stale.
Don't use nolocks wrong. But they do have a place. If you are going to cut a check when a byte is set to 1 and then set it to 2 when the check is cut - not a time for a nolock.
answered Sep 26 '12 at 22:31
paparazzopaparazzo
37.6k1675138
1
Thank you. We see similar performance characteristics. Our site wouldn't run if we required locks for reads, and the impact of not having it in most cases is just insignificant.
– Brian White
Sep 27 '12 at 2:00
@BrianWhite Thank you. Someone gets it. And I take a lot of table locks on update and insert. Get in, get it done, and get out is my approach.
– paparazzo
Sep 27 '12 at 2:21
1
Dirty read (nolock) just sounds dirty. You are never going to get partial data. If an update is changing John to Sally you are never going to get Jolly. - we read John right?
– MonsterMMORPG
Jul 18 '16 at 12:54
1
Updates in sql server use update lock (U) that is later converted to exclusive lock (X). (see madeiradata.com/role-update-lock-sql-server) Update lock doesn't block shared locks, but exclusive lock blocks all other locks (see msdn.microsoft.com/en-us/library/ms186396(v=sql.105).aspx).
– kolobok
Feb 21 '18 at 15:56
@kolobok going to take an update a while to get an exclusive lock
– paparazzo
Feb 21 '18 at 16:13
add a comment |
On performance you keep focusing on select.
Shared does not block reads.
Shared lock blocks update.
If you have hundreds of shared locks it is going to take an update a while to get an exclusive lock as it must wait for shared locks to clear.
By default a select (read) takes a shared lock.
Shared (S) locks allow concurrent transactions to read (SELECT) a resource.
A shared lock as no effect on other selects (1 or a 1000).
The difference is how the nolock versus shared lock effects update or insert operation.
No other transactions can modify the data while shared (S) locks exist on the resource.
A shared lock blocks an update!
But nolock does not block an update.
This can have huge impacts on performance of updates. It also impact inserts.
Dirty read (nolock) just sounds dirty. You are never going to get partial data. If an update is changing John to Sally you are never going to get Jolly.
I use shared locks a lot for concurrency. Data is stale as soon as it is read. A read of John that changes to Sally the next millisecond is stale data. A read of Sally that gets rolled back John the next millisecond is stale data. That is on the millisecond level. I have a dataloader that take 20 hours to run if users are taking shared locks and 4 hours to run is users are taking no lock. Shared locks in this case cause data to be 16 hours stale.
Don't use nolocks wrong. But they do have a place. If you are going to cut a check when a byte is set to 1 and then set it to 2 when the check is cut - not a time for a nolock.
answered Sep 26 '12 at 22:31
paparazzopaparazzo
37.6k1675138
1
Thank you. We see similar performance characteristics. Our site wouldn't run if we required locks for reads, and the impact of not having it in most cases is just insignificant.
– Brian White
Sep 27 '12 at 2:00
@BrianWhite Thank you. Someone gets it. And I take a lot of table locks on update and insert. Get in, get it done, and get out is my approach.
– paparazzo
Sep 27 '12 at 2:21
1
Dirty read (nolock) just sounds dirty. You are never going to get partial data. If an update is changing John to Sally you are never going to get Jolly. - we read John right?
– MonsterMMORPG
Jul 18 '16 at 12:54
1
Updates in sql server use update lock (U) that is later converted to exclusive lock (X). (see madeiradata.com/role-update-lock-sql-server) Update lock doesn't block shared locks, but exclusive lock blocks all other locks (see msdn.microsoft.com/en-us/library/ms186396(v=sql.105).aspx).
– kolobok
Feb 21 '18 at 15:56
@kolobok going to take an update a while to get an exclusive lock
– paparazzo
Feb 21 '18 at 16:13
add a comment |
On performance you keep focusing on select.
Shared does not block reads.
Shared lock blocks update.
If you have hundreds of shared locks it is going to take an update a while to get an exclusive lock as it must wait for shared locks to clear.
By default a select (read) takes a shared lock.
Shared (S) locks allow concurrent transactions to read (SELECT) a resource.
A shared lock as no effect on other selects (1 or a 1000).
The difference is how the nolock versus shared lock effects update or insert operation.
No other transactions can modify the data while shared (S) locks exist on the resource.
A shared lock blocks an update!
But nolock does not block an update.
This can have huge impacts on performance of updates. It also impact inserts.
Dirty read (nolock) just sounds dirty. You are never going to get partial data. If an update is changing John to Sally you are never going to get Jolly.
I use shared locks a lot for concurrency. Data is stale as soon as it is read. A read of John that changes to Sally the next millisecond is stale data. A read of Sally that gets rolled back John the next millisecond is stale data. That is on the millisecond level. I have a dataloader that take 20 hours to run if users are taking shared locks and 4 hours to run is users are taking no lock. Shared locks in this case cause data to be 16 hours stale.
Don't use nolocks wrong. But they do have a place. If you are going to cut a check when a byte is set to 1 and then set it to 2 when the check is cut - not a time for a nolock.
answered Sep 26 '12 at 22:31
paparazzopaparazzo
37.6k1675138
On performance you keep focusing on select.
Shared does not block reads.
Shared lock blocks update.
If you have hundreds of shared locks it is going to take an update a while to get an exclusive lock as it must wait for shared locks to clear.
By default a select (read) takes a shared lock.
Shared (S) locks allow concurrent transactions to read (SELECT) a resource.
A shared lock as no effect on other selects (1 or a 1000).
The difference is how the nolock versus shared lock effects update or insert operation.
No other transactions can modify the data while shared (S) locks exist on the resource.
A shared lock blocks an update!
But nolock does not block an update.
This can have huge impacts on performance of updates. It also impact inserts.
Dirty read (nolock) just sounds dirty. You are never going to get partial data. If an update is changing John to Sally you are never going to get Jolly.
I use shared locks a lot for concurrency. Data is stale as soon as it is read. A read of John that changes to Sally the next millisecond is stale data. A read of Sally that gets rolled back John the next millisecond is stale data. That is on the millisecond level. I have a dataloader that take 20 hours to run if users are taking shared locks and 4 hours to run is users are taking no lock. Shared locks in this case cause data to be 16 hours stale.
Don't use nolocks wrong. But they do have a place. If you are going to cut a check when a byte is set to 1 and then set it to 2 when the check is cut - not a time for a nolock.
answered Sep 26 '12 at 22:31
paparazzopaparazzo
37.6k1675138
edited Dec 24 '13 at 16:14
answered Sep 26 '12 at 22:31
paparazzopaparazzo
37.6k1675138
answered Sep 26 '12 at 22:31
paparazzopaparazzo
37.6k1675138
answered Sep 26 '12 at 22:31
paparazzopaparazzo
37.6k1675138
37.6k1675138
1
Thank you. We see similar performance characteristics. Our site wouldn't run if we required locks for reads, and the impact of not having it in most cases is just insignificant.
– Brian White
Sep 27 '12 at 2:00
@BrianWhite Thank you. Someone gets it. And I take a lot of table locks on update and insert. Get in, get it done, and get out is my approach.
– paparazzo
Sep 27 '12 at 2:21
1
Dirty read (nolock) just sounds dirty. You are never going to get partial data. If an update is changing John to Sally you are never going to get Jolly. - we read John right?
– MonsterMMORPG
Jul 18 '16 at 12:54
1
Updates in sql server use update lock (U) that is later converted to exclusive lock (X). (see madeiradata.com/role-update-lock-sql-server) Update lock doesn't block shared locks, but exclusive lock blocks all other locks (see msdn.microsoft.com/en-us/library/ms186396(v=sql.105).aspx).
– kolobok
Feb 21 '18 at 15:56
@kolobok going to take an update a while to get an exclusive lock
– paparazzo
Feb 21 '18 at 16:13
add a comment |
1
Thank you. We see similar performance characteristics. Our site wouldn't run if we required locks for reads, and the impact of not having it in most cases is just insignificant.
– Brian White
Sep 27 '12 at 2:00
@BrianWhite Thank you. Someone gets it. And I take a lot of table locks on update and insert. Get in, get it done, and get out is my approach.
– paparazzo
Sep 27 '12 at 2:21
1
Dirty read (nolock) just sounds dirty. You are never going to get partial data. If an update is changing John to Sally you are never going to get Jolly. - we read John right?
– MonsterMMORPG
Jul 18 '16 at 12:54
1
Updates in sql server use update lock (U) that is later converted to exclusive lock (X). (see madeiradata.com/role-update-lock-sql-server) Update lock doesn't block shared locks, but exclusive lock blocks all other locks (see msdn.microsoft.com/en-us/library/ms186396(v=sql.105).aspx).
– kolobok
Feb 21 '18 at 15:56
@kolobok going to take an update a while to get an exclusive lock
– paparazzo
Feb 21 '18 at 16:13
1
1
Thank you. We see similar performance characteristics. Our site wouldn't run if we required locks for reads, and the impact of not having it in most cases is just insignificant.
– Brian White
Sep 27 '12 at 2:00
Thank you. We see similar performance characteristics. Our site wouldn't run if we required locks for reads, and the impact of not having it in most cases is just insignificant.
– Brian White
Sep 27 '12 at 2:00
@BrianWhite Thank you. Someone gets it. And I take a lot of table locks on update and insert. Get in, get it done, and get out is my approach.
– paparazzo
Sep 27 '12 at 2:21
@BrianWhite Thank you. Someone gets it. And I take a lot of table locks on update and insert. Get in, get it done, and get out is my approach.
– paparazzo
Sep 27 '12 at 2:21
1
1
Dirty read (nolock) just sounds dirty. You are never going to get partial data. If an update is changing John to Sally you are never going to get Jolly. - we read John right?
– MonsterMMORPG
Jul 18 '16 at 12:54
Dirty read (nolock) just sounds dirty. You are never going to get partial data. If an update is changing John to Sally you are never going to get Jolly. - we read John right?
– MonsterMMORPG
Jul 18 '16 at 12:54
1
1
Updates in sql server use update lock (U) that is later converted to exclusive lock (X). (see madeiradata.com/role-update-lock-sql-server) Update lock doesn't block shared locks, but exclusive lock blocks all other locks (see msdn.microsoft.com/en-us/library/ms186396(v=sql.105).aspx).
– kolobok
Feb 21 '18 at 15:56
Updates in sql server use update lock (U) that is later converted to exclusive lock (X). (see madeiradata.com/role-update-lock-sql-server) Update lock doesn't block shared locks, but exclusive lock blocks all other locks (see msdn.microsoft.com/en-us/library/ms186396(v=sql.105).aspx).
– kolobok
Feb 21 '18 at 15:56
@kolobok going to take an update a while to get an exclusive lock
– paparazzo
Feb 21 '18 at 16:13
@kolobok going to take an update a while to get an exclusive lock
– paparazzo
Feb 21 '18 at 16:13
add a comment |
At my work, we have a very big system that runs on many PCs at the same time, with very big tables with hundreds of thousands of rows, and sometimes many millions of rows.
When you make a SELECT on a very big table, let's say you want to know every transaction a user has made in the past 10 years, and the primary key of the table is not built in an efficient way, the query might take several minutes to run.
Then, our application might me running on many user's PCs at the same time, accessing the same database. So if someone tries to insert into the table that the other SELECT is reading (in pages that SQL is trying to read), then a LOCK can occur and the two transactions block each other.
We had to add a "NO LOCK" to our SELECT statement, because it was a huge SELECT on a table that is used a lot by a lot of users at the same time and we had LOCKS all the time.
I don't know if my example is clear enough? This is a real life example.
edited Apr 21 '15 at 10:29
JonnyRaa
4,65213236
answered Sep 26 '12 at 19:34
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,023922
Thank you for the example, but I'm only wondering about SELECT queries affecting other SELECT queries (on that same table)..
– Francis P
Sep 26 '12 at 19:36
1
They won't, but a select statement could be part of transaction that includes an update. Update tbl set x = (select max(y) from tbl) where z = (select min(a) from tbl). If you have a concurrent select z from tbl well the other selects aren't blocking it, but the update is.
– Brian White
Sep 27 '12 at 2:03
1
I had exactly this issue that a long running select was blocking my inserts
– nojetlag
Jul 3 '13 at 14:13
2
The transactions won't block each other - the select will block the update. Here's a couple of interesting links that have just helped me understand a little more about how this stuff works: first one second one
– JonnyRaa
Apr 21 '15 at 9:54
@JonnyLeeds : Your 2nd link doesn't work anymore. Here is an archived link of SQL Server: Locking basics
– stomy
Apr 19 '18 at 15:36
add a comment |
At my work, we have a very big system that runs on many PCs at the same time, with very big tables with hundreds of thousands of rows, and sometimes many millions of rows.
When you make a SELECT on a very big table, let's say you want to know every transaction a user has made in the past 10 years, and the primary key of the table is not built in an efficient way, the query might take several minutes to run.
Then, our application might me running on many user's PCs at the same time, accessing the same database. So if someone tries to insert into the table that the other SELECT is reading (in pages that SQL is trying to read), then a LOCK can occur and the two transactions block each other.
We had to add a "NO LOCK" to our SELECT statement, because it was a huge SELECT on a table that is used a lot by a lot of users at the same time and we had LOCKS all the time.
I don't know if my example is clear enough? This is a real life example.
edited Apr 21 '15 at 10:29
JonnyRaa
4,65213236
answered Sep 26 '12 at 19:34
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,023922
Thank you for the example, but I'm only wondering about SELECT queries affecting other SELECT queries (on that same table)..
– Francis P
Sep 26 '12 at 19:36
1
They won't, but a select statement could be part of transaction that includes an update. Update tbl set x = (select max(y) from tbl) where z = (select min(a) from tbl). If you have a concurrent select z from tbl well the other selects aren't blocking it, but the update is.
– Brian White
Sep 27 '12 at 2:03
1
I had exactly this issue that a long running select was blocking my inserts
– nojetlag
Jul 3 '13 at 14:13
2
The transactions won't block each other - the select will block the update. Here's a couple of interesting links that have just helped me understand a little more about how this stuff works: first one second one
– JonnyRaa
Apr 21 '15 at 9:54
@JonnyLeeds : Your 2nd link doesn't work anymore. Here is an archived link of SQL Server: Locking basics
– stomy
Apr 19 '18 at 15:36
add a comment |
At my work, we have a very big system that runs on many PCs at the same time, with very big tables with hundreds of thousands of rows, and sometimes many millions of rows.
When you make a SELECT on a very big table, let's say you want to know every transaction a user has made in the past 10 years, and the primary key of the table is not built in an efficient way, the query might take several minutes to run.
Then, our application might me running on many user's PCs at the same time, accessing the same database. So if someone tries to insert into the table that the other SELECT is reading (in pages that SQL is trying to read), then a LOCK can occur and the two transactions block each other.
We had to add a "NO LOCK" to our SELECT statement, because it was a huge SELECT on a table that is used a lot by a lot of users at the same time and we had LOCKS all the time.
I don't know if my example is clear enough? This is a real life example.
edited Apr 21 '15 at 10:29
JonnyRaa
4,65213236
answered Sep 26 '12 at 19:34
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,023922
At my work, we have a very big system that runs on many PCs at the same time, with very big tables with hundreds of thousands of rows, and sometimes many millions of rows.
When you make a SELECT on a very big table, let's say you want to know every transaction a user has made in the past 10 years, and the primary key of the table is not built in an efficient way, the query might take several minutes to run.
Then, our application might me running on many user's PCs at the same time, accessing the same database. So if someone tries to insert into the table that the other SELECT is reading (in pages that SQL is trying to read), then a LOCK can occur and the two transactions block each other.
We had to add a "NO LOCK" to our SELECT statement, because it was a huge SELECT on a table that is used a lot by a lot of users at the same time and we had LOCKS all the time.
I don't know if my example is clear enough? This is a real life example.
edited Apr 21 '15 at 10:29
JonnyRaa
4,65213236
answered Sep 26 '12 at 19:34
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,023922
edited Apr 21 '15 at 10:29
JonnyRaa
4,65213236
edited Apr 21 '15 at 10:29
JonnyRaa
4,65213236
edited Apr 21 '15 at 10:29
JonnyRaa
4,65213236
4,65213236
answered Sep 26 '12 at 19:34
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,023922
answered Sep 26 '12 at 19:34
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,023922
answered Sep 26 '12 at 19:34
Danielle Paquette-HarveyDanielle Paquette-Harvey
1,023922
1,023922
Thank you for the example, but I'm only wondering about SELECT queries affecting other SELECT queries (on that same table)..
– Francis P
Sep 26 '12 at 19:36
1
They won't, but a select statement could be part of transaction that includes an update. Update tbl set x = (select max(y) from tbl) where z = (select min(a) from tbl). If you have a concurrent select z from tbl well the other selects aren't blocking it, but the update is.
– Brian White
Sep 27 '12 at 2:03
1
I had exactly this issue that a long running select was blocking my inserts
– nojetlag
Jul 3 '13 at 14:13
2
The transactions won't block each other - the select will block the update. Here's a couple of interesting links that have just helped me understand a little more about how this stuff works: first one second one
– JonnyRaa
Apr 21 '15 at 9:54
@JonnyLeeds : Your 2nd link doesn't work anymore. Here is an archived link of SQL Server: Locking basics
– stomy
Apr 19 '18 at 15:36
add a comment |
Thank you for the example, but I'm only wondering about SELECT queries affecting other SELECT queries (on that same table)..
– Francis P
Sep 26 '12 at 19:36
1
They won't, but a select statement could be part of transaction that includes an update. Update tbl set x = (select max(y) from tbl) where z = (select min(a) from tbl). If you have a concurrent select z from tbl well the other selects aren't blocking it, but the update is.
– Brian White
Sep 27 '12 at 2:03
1
I had exactly this issue that a long running select was blocking my inserts
– nojetlag
Jul 3 '13 at 14:13
2
The transactions won't block each other - the select will block the update. Here's a couple of interesting links that have just helped me understand a little more about how this stuff works: first one second one
– JonnyRaa
Apr 21 '15 at 9:54
@JonnyLeeds : Your 2nd link doesn't work anymore. Here is an archived link of SQL Server: Locking basics
– stomy
Apr 19 '18 at 15:36
Thank you for the example, but I'm only wondering about SELECT queries affecting other SELECT queries (on that same table)..
– Francis P
Sep 26 '12 at 19:36
Thank you for the example, but I'm only wondering about SELECT queries affecting other SELECT queries (on that same table)..
– Francis P
Sep 26 '12 at 19:36
1
1
They won't, but a select statement could be part of transaction that includes an update. Update tbl set x = (select max(y) from tbl) where z = (select min(a) from tbl). If you have a concurrent select z from tbl well the other selects aren't blocking it, but the update is.
– Brian White
Sep 27 '12 at 2:03
They won't, but a select statement could be part of transaction that includes an update. Update tbl set x = (select max(y) from tbl) where z = (select min(a) from tbl). If you have a concurrent select z from tbl well the other selects aren't blocking it, but the update is.
– Brian White
Sep 27 '12 at 2:03
1
1
I had exactly this issue that a long running select was blocking my inserts
– nojetlag
Jul 3 '13 at 14:13
I had exactly this issue that a long running select was blocking my inserts
– nojetlag
Jul 3 '13 at 14:13
2
2
The transactions won't block each other - the select will block the update. Here's a couple of interesting links that have just helped me understand a little more about how this stuff works: first one second one
– JonnyRaa
Apr 21 '15 at 9:54
The transactions won't block each other - the select will block the update. Here's a couple of interesting links that have just helped me understand a little more about how this stuff works: first one second one
– JonnyRaa
Apr 21 '15 at 9:54
@JonnyLeeds : Your 2nd link doesn't work anymore. Here is an archived link of SQL Server: Locking basics
– stomy
Apr 19 '18 at 15:36
@JonnyLeeds : Your 2nd link doesn't work anymore. Here is an archived link of SQL Server: Locking basics
– stomy
Apr 19 '18 at 15:36
add a comment |
I have to add an important comment. Everyone is mentioning that NOLOCKreads only dirty data. This is not precise. It is also possible that you'll get same row twice or whole row is skipped during your read. Reason is that you could ask for some data in same time when SQL Server is re-balancing b-tree.
Check another threads
https://stackoverflow.com/a/5469238/2108874
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii.aspx)
With the NOLOCK hint (or setting the isolation level of the session to READ UNCOMMITTED) you tell SQL Server that you don't expect consistency, so there are no guarantees. Bear in mind though that "inconsistent data" does not only mean that you might see uncommitted changes that were later rolled back, or data changes in an intermediate state of the transaction. It also means that in a simple query that scans all table/index data SQL Server may lose the scan position, or you might end up getting the same row twice.
edited May 23 '17 at 11:47
Community♦
11
answered Mar 4 '15 at 8:59
Milan MatějkaMilan Matějka
2,0441319
add a comment |
I have to add an important comment. Everyone is mentioning that NOLOCKreads only dirty data. This is not precise. It is also possible that you'll get same row twice or whole row is skipped during your read. Reason is that you could ask for some data in same time when SQL Server is re-balancing b-tree.
Check another threads
https://stackoverflow.com/a/5469238/2108874
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii.aspx)
With the NOLOCK hint (or setting the isolation level of the session to READ UNCOMMITTED) you tell SQL Server that you don't expect consistency, so there are no guarantees. Bear in mind though that "inconsistent data" does not only mean that you might see uncommitted changes that were later rolled back, or data changes in an intermediate state of the transaction. It also means that in a simple query that scans all table/index data SQL Server may lose the scan position, or you might end up getting the same row twice.
edited May 23 '17 at 11:47
Community♦
11
answered Mar 4 '15 at 8:59
Milan MatějkaMilan Matějka
2,0441319
add a comment |
I have to add an important comment. Everyone is mentioning that NOLOCKreads only dirty data. This is not precise. It is also possible that you'll get same row twice or whole row is skipped during your read. Reason is that you could ask for some data in same time when SQL Server is re-balancing b-tree.
Check another threads
https://stackoverflow.com/a/5469238/2108874
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii.aspx)
With the NOLOCK hint (or setting the isolation level of the session to READ UNCOMMITTED) you tell SQL Server that you don't expect consistency, so there are no guarantees. Bear in mind though that "inconsistent data" does not only mean that you might see uncommitted changes that were later rolled back, or data changes in an intermediate state of the transaction. It also means that in a simple query that scans all table/index data SQL Server may lose the scan position, or you might end up getting the same row twice.
edited May 23 '17 at 11:47
Community♦
11
answered Mar 4 '15 at 8:59
Milan MatějkaMilan Matějka
2,0441319
I have to add an important comment. Everyone is mentioning that NOLOCKreads only dirty data. This is not precise. It is also possible that you'll get same row twice or whole row is skipped during your read. Reason is that you could ask for some data in same time when SQL Server is re-balancing b-tree.
Check another threads
https://stackoverflow.com/a/5469238/2108874
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii.aspx)
With the NOLOCK hint (or setting the isolation level of the session to READ UNCOMMITTED) you tell SQL Server that you don't expect consistency, so there are no guarantees. Bear in mind though that "inconsistent data" does not only mean that you might see uncommitted changes that were later rolled back, or data changes in an intermediate state of the transaction. It also means that in a simple query that scans all table/index data SQL Server may lose the scan position, or you might end up getting the same row twice.
edited May 23 '17 at 11:47
Community♦
11
answered Mar 4 '15 at 8:59
Milan MatějkaMilan Matějka
2,0441319
edited May 23 '17 at 11:47
Community♦
11
edited May 23 '17 at 11:47
Community♦
11
edited May 23 '17 at 11:47
Community♦
11
11
answered Mar 4 '15 at 8:59
Milan MatějkaMilan Matějka
2,0441319
answered Mar 4 '15 at 8:59
Milan MatějkaMilan Matějka
2,0441319
answered Mar 4 '15 at 8:59
Milan MatějkaMilan Matějka
2,0441319
2,0441319
add a comment |
add a comment |
The SELECT WITH (NOLOCK) allows reads of uncommitted data, which is equivalent to having the READ UNCOMMITTED isolation level set on your database. The NOLOCK keyword allows finer grained control than setting the isolation level on the entire database.
Wikipedia has a useful article: Wikipedia: Isolation (database systems)
It is also discussed at length in other stackoverflow articles.
answered Dec 5 '14 at 8:42
rghomerghome
4,81462641
Thanks rghome for the additional information you provided.
– Francis P
Dec 5 '14 at 16:03
This is why I prefer using theREADUNCOMMITTED(an alias forNOLOCK) hint, when such is a valid use-case. Doing so makes the actual operation, which isn't really "without locks", less unclear.
– user2864740
Dec 16 '15 at 22:43
add a comment |
The SELECT WITH (NOLOCK) allows reads of uncommitted data, which is equivalent to having the READ UNCOMMITTED isolation level set on your database. The NOLOCK keyword allows finer grained control than setting the isolation level on the entire database.
Wikipedia has a useful article: Wikipedia: Isolation (database systems)
It is also discussed at length in other stackoverflow articles.
answered Dec 5 '14 at 8:42
rghomerghome
4,81462641
Thanks rghome for the additional information you provided.
– Francis P
Dec 5 '14 at 16:03
This is why I prefer using theREADUNCOMMITTED(an alias forNOLOCK) hint, when such is a valid use-case. Doing so makes the actual operation, which isn't really "without locks", less unclear.
– user2864740
Dec 16 '15 at 22:43
add a comment |
The SELECT WITH (NOLOCK) allows reads of uncommitted data, which is equivalent to having the READ UNCOMMITTED isolation level set on your database. The NOLOCK keyword allows finer grained control than setting the isolation level on the entire database.
Wikipedia has a useful article: Wikipedia: Isolation (database systems)
It is also discussed at length in other stackoverflow articles.
answered Dec 5 '14 at 8:42
rghomerghome
4,81462641
The SELECT WITH (NOLOCK) allows reads of uncommitted data, which is equivalent to having the READ UNCOMMITTED isolation level set on your database. The NOLOCK keyword allows finer grained control than setting the isolation level on the entire database.
Wikipedia has a useful article: Wikipedia: Isolation (database systems)
It is also discussed at length in other stackoverflow articles.
answered Dec 5 '14 at 8:42
rghomerghome
4,81462641
answered Dec 5 '14 at 8:42
rghomerghome
4,81462641
answered Dec 5 '14 at 8:42
rghomerghome
4,81462641
answered Dec 5 '14 at 8:42
rghomerghome
4,81462641
4,81462641
Thanks rghome for the additional information you provided.
– Francis P
Dec 5 '14 at 16:03
This is why I prefer using theREADUNCOMMITTED(an alias forNOLOCK) hint, when such is a valid use-case. Doing so makes the actual operation, which isn't really "without locks", less unclear.
– user2864740
Dec 16 '15 at 22:43
add a comment |
Thanks rghome for the additional information you provided.
– Francis P
Dec 5 '14 at 16:03
This is why I prefer using theREADUNCOMMITTED(an alias forNOLOCK) hint, when such is a valid use-case. Doing so makes the actual operation, which isn't really "without locks", less unclear.
– user2864740
Dec 16 '15 at 22:43
Thanks rghome for the additional information you provided.
– Francis P
Dec 5 '14 at 16:03
Thanks rghome for the additional information you provided.
– Francis P
Dec 5 '14 at 16:03
This is why I prefer using the
READUNCOMMITTED (an alias for NOLOCK) hint, when such is a valid use-case. Doing so makes the actual operation, which isn't really "without locks", less unclear.– user2864740
Dec 16 '15 at 22:43
This is why I prefer using the
READUNCOMMITTED (an alias for NOLOCK) hint, when such is a valid use-case. Doing so makes the actual operation, which isn't really "without locks", less unclear.– user2864740
Dec 16 '15 at 22:43
add a comment |
select with no lock - will select records which may / may not going to be inserted. you will read a dirty data.
for example - lets say a transaction insert 1000 rows and then fails.
when you select - you will get the 1000 rows.
answered Sep 26 '12 at 19:15
Royi NamirRoyi Namir
75.5k98332591
But what if no record is intended to be inserted in that table, is the NO LOCK still relevant?
– Francis P
Sep 26 '12 at 19:21
no it is not. since read uses a shared lock which can be acquired by more than 1 sessions. there is no way of getting dirty data.
– Royi Namir
Sep 26 '12 at 19:23
And on a performance POV?
– Francis P
Sep 26 '12 at 19:28
I rather not to answer on a thing I'm not sure of. :-)
– Royi Namir
Sep 26 '12 at 19:29
add a comment |
select with no lock - will select records which may / may not going to be inserted. you will read a dirty data.
for example - lets say a transaction insert 1000 rows and then fails.
when you select - you will get the 1000 rows.
answered Sep 26 '12 at 19:15
Royi NamirRoyi Namir
75.5k98332591
But what if no record is intended to be inserted in that table, is the NO LOCK still relevant?
– Francis P
Sep 26 '12 at 19:21
no it is not. since read uses a shared lock which can be acquired by more than 1 sessions. there is no way of getting dirty data.
– Royi Namir
Sep 26 '12 at 19:23
And on a performance POV?
– Francis P
Sep 26 '12 at 19:28
I rather not to answer on a thing I'm not sure of. :-)
– Royi Namir
Sep 26 '12 at 19:29
add a comment |
select with no lock - will select records which may / may not going to be inserted. you will read a dirty data.
for example - lets say a transaction insert 1000 rows and then fails.
when you select - you will get the 1000 rows.
answered Sep 26 '12 at 19:15
Royi NamirRoyi Namir
75.5k98332591
select with no lock - will select records which may / may not going to be inserted. you will read a dirty data.
for example - lets say a transaction insert 1000 rows and then fails.
when you select - you will get the 1000 rows.
answered Sep 26 '12 at 19:15
Royi NamirRoyi Namir
75.5k98332591
answered Sep 26 '12 at 19:15
Royi NamirRoyi Namir
75.5k98332591
answered Sep 26 '12 at 19:15
Royi NamirRoyi Namir
75.5k98332591
answered Sep 26 '12 at 19:15
Royi NamirRoyi Namir
75.5k98332591
75.5k98332591
But what if no record is intended to be inserted in that table, is the NO LOCK still relevant?
– Francis P
Sep 26 '12 at 19:21
no it is not. since read uses a shared lock which can be acquired by more than 1 sessions. there is no way of getting dirty data.
– Royi Namir
Sep 26 '12 at 19:23
And on a performance POV?
– Francis P
Sep 26 '12 at 19:28
I rather not to answer on a thing I'm not sure of. :-)
– Royi Namir
Sep 26 '12 at 19:29
add a comment |
But what if no record is intended to be inserted in that table, is the NO LOCK still relevant?
– Francis P
Sep 26 '12 at 19:21
no it is not. since read uses a shared lock which can be acquired by more than 1 sessions. there is no way of getting dirty data.
– Royi Namir
Sep 26 '12 at 19:23
And on a performance POV?
– Francis P
Sep 26 '12 at 19:28
I rather not to answer on a thing I'm not sure of. :-)
– Royi Namir
Sep 26 '12 at 19:29
But what if no record is intended to be inserted in that table, is the NO LOCK still relevant?
– Francis P
Sep 26 '12 at 19:21
But what if no record is intended to be inserted in that table, is the NO LOCK still relevant?
– Francis P
Sep 26 '12 at 19:21
no it is not. since read uses a shared lock which can be acquired by more than 1 sessions. there is no way of getting dirty data.
– Royi Namir
Sep 26 '12 at 19:23
no it is not. since read uses a shared lock which can be acquired by more than 1 sessions. there is no way of getting dirty data.
– Royi Namir
Sep 26 '12 at 19:23
And on a performance POV?
– Francis P
Sep 26 '12 at 19:28
And on a performance POV?
– Francis P
Sep 26 '12 at 19:28
I rather not to answer on a thing I'm not sure of. :-)
– Royi Namir
Sep 26 '12 at 19:29
I rather not to answer on a thing I'm not sure of. :-)
– Royi Namir
Sep 26 '12 at 19:29
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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%2f12608780%2funderstanding-sql-server-locks-on-select-queries%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
