Is long running select locking updates on sql server
We have a monitoring query for our admins that they run directly in the management studio. This query is a long running query that analyses all records in a table. We have noticed now that when this query is running incoming updates a blocked until this query is finished. After a little investigation we have seen that on sql server select does a shared lock that blocks exclusive locks and that an update does a update lock and then a exclusive lock.
https://www.sqlpassion.at/archive/2014/07/28/why-do-we-need-update-locks-in-sql-server/#comment-104367
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186396(v=sql.105)
Is this a behavior that we can change? What is the best practice to avoid this situation if we have a public portal as frontend where we have many selects also a little bit longer running and different updates on the same table?
sql
sql-server asked Nov 21 '18 at 8:38
cpiockcpiock
526723
|
show 5 more comments
We have a monitoring query for our admins that they run directly in the management studio. This query is a long running query that analyses all records in a table. We have noticed now that when this query is running incoming updates a blocked until this query is finished. After a little investigation we have seen that on sql server select does a shared lock that blocks exclusive locks and that an update does a update lock and then a exclusive lock.
https://www.sqlpassion.at/archive/2014/07/28/why-do-we-need-update-locks-in-sql-server/#comment-104367
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186396(v=sql.105)
Is this a behavior that we can change? What is the best practice to avoid this situation if we have a public portal as frontend where we have many selects also a little bit longer running and different updates on the same table?
sql
sql-server asked Nov 21 '18 at 8:38
cpiockcpiock
526723
3
Ideally you would improve the performance of your queries such that they are quick. However as a purely monitoring function you might decide you can get away without taking a shared lock using the query hint ` with (nolock)`. docs.microsoft.com/en-us/sql/t-sql/queries/…. I would not recommend that approach for you public facing site queries - they need to be improved.
– Dale Burrell
Nov 21 '18 at 9:08
1
Did you try the snapshot isolation for long running select queries?
– serge
Nov 21 '18 at 9:12
@DaleBurrell this means best practices to avoid locks of this kind is to have fast queries or to divide update statements and sql statements on different dbs?
– cpiock
Nov 21 '18 at 9:13
@serge i have seen that on snapshot isolation have to be careful because the need extra space for the tempdb right?
– cpiock
Nov 21 '18 at 9:15
4
Best practice is definitely to have fast queries :) However in cases where this isn't possible, e.g. reporting or similar, possible solutions are to run the query when the database isn't busy, run the query against a copy of database, have copy functions which build the report data, maybe to another database. I would suggest that analysing every record in a table is probably not something you want to do on a live database.
– Dale Burrell
Nov 21 '18 at 9:18
|
show 5 more comments
We have a monitoring query for our admins that they run directly in the management studio. This query is a long running query that analyses all records in a table. We have noticed now that when this query is running incoming updates a blocked until this query is finished. After a little investigation we have seen that on sql server select does a shared lock that blocks exclusive locks and that an update does a update lock and then a exclusive lock.
https://www.sqlpassion.at/archive/2014/07/28/why-do-we-need-update-locks-in-sql-server/#comment-104367
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186396(v=sql.105)
Is this a behavior that we can change? What is the best practice to avoid this situation if we have a public portal as frontend where we have many selects also a little bit longer running and different updates on the same table?
sql
sql-server asked Nov 21 '18 at 8:38
cpiockcpiock
526723
We have a monitoring query for our admins that they run directly in the management studio. This query is a long running query that analyses all records in a table. We have noticed now that when this query is running incoming updates a blocked until this query is finished. After a little investigation we have seen that on sql server select does a shared lock that blocks exclusive locks and that an update does a update lock and then a exclusive lock.
https://www.sqlpassion.at/archive/2014/07/28/why-do-we-need-update-locks-in-sql-server/#comment-104367
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186396(v=sql.105)
Is this a behavior that we can change? What is the best practice to avoid this situation if we have a public portal as frontend where we have many selects also a little bit longer running and different updates on the same table?
sql
sql-server sql
sql-server asked Nov 21 '18 at 8:38
cpiockcpiock
526723
asked Nov 21 '18 at 8:38
cpiockcpiock
526723
asked Nov 21 '18 at 8:38
cpiockcpiock
526723
asked Nov 21 '18 at 8:38
cpiockcpiock
526723
asked Nov 21 '18 at 8:38
cpiockcpiock
526723
526723
3
Ideally you would improve the performance of your queries such that they are quick. However as a purely monitoring function you might decide you can get away without taking a shared lock using the query hint ` with (nolock)`. docs.microsoft.com/en-us/sql/t-sql/queries/…. I would not recommend that approach for you public facing site queries - they need to be improved.
– Dale Burrell
Nov 21 '18 at 9:08
1
Did you try the snapshot isolation for long running select queries?
– serge
Nov 21 '18 at 9:12
@DaleBurrell this means best practices to avoid locks of this kind is to have fast queries or to divide update statements and sql statements on different dbs?
– cpiock
Nov 21 '18 at 9:13
@serge i have seen that on snapshot isolation have to be careful because the need extra space for the tempdb right?
– cpiock
Nov 21 '18 at 9:15
4
Best practice is definitely to have fast queries :) However in cases where this isn't possible, e.g. reporting or similar, possible solutions are to run the query when the database isn't busy, run the query against a copy of database, have copy functions which build the report data, maybe to another database. I would suggest that analysing every record in a table is probably not something you want to do on a live database.
– Dale Burrell
Nov 21 '18 at 9:18
|
show 5 more comments
3
Ideally you would improve the performance of your queries such that they are quick. However as a purely monitoring function you might decide you can get away without taking a shared lock using the query hint ` with (nolock)`. docs.microsoft.com/en-us/sql/t-sql/queries/…. I would not recommend that approach for you public facing site queries - they need to be improved.
– Dale Burrell
Nov 21 '18 at 9:08
1
Did you try the snapshot isolation for long running select queries?
– serge
Nov 21 '18 at 9:12
@DaleBurrell this means best practices to avoid locks of this kind is to have fast queries or to divide update statements and sql statements on different dbs?
– cpiock
Nov 21 '18 at 9:13
@serge i have seen that on snapshot isolation have to be careful because the need extra space for the tempdb right?
– cpiock
Nov 21 '18 at 9:15
4
Best practice is definitely to have fast queries :) However in cases where this isn't possible, e.g. reporting or similar, possible solutions are to run the query when the database isn't busy, run the query against a copy of database, have copy functions which build the report data, maybe to another database. I would suggest that analysing every record in a table is probably not something you want to do on a live database.
– Dale Burrell
Nov 21 '18 at 9:18
3
3
Ideally you would improve the performance of your queries such that they are quick. However as a purely monitoring function you might decide you can get away without taking a shared lock using the query hint ` with (nolock)`. docs.microsoft.com/en-us/sql/t-sql/queries/…. I would not recommend that approach for you public facing site queries - they need to be improved.
– Dale Burrell
Nov 21 '18 at 9:08
Ideally you would improve the performance of your queries such that they are quick. However as a purely monitoring function you might decide you can get away without taking a shared lock using the query hint ` with (nolock)`. docs.microsoft.com/en-us/sql/t-sql/queries/…. I would not recommend that approach for you public facing site queries - they need to be improved.
– Dale Burrell
Nov 21 '18 at 9:08
1
1
Did you try the snapshot isolation for long running select queries?
– serge
Nov 21 '18 at 9:12
Did you try the snapshot isolation for long running select queries?
– serge
Nov 21 '18 at 9:12
@DaleBurrell this means best practices to avoid locks of this kind is to have fast queries or to divide update statements and sql statements on different dbs?
– cpiock
Nov 21 '18 at 9:13
@DaleBurrell this means best practices to avoid locks of this kind is to have fast queries or to divide update statements and sql statements on different dbs?
– cpiock
Nov 21 '18 at 9:13
@serge i have seen that on snapshot isolation have to be careful because the need extra space for the tempdb right?
– cpiock
Nov 21 '18 at 9:15
@serge i have seen that on snapshot isolation have to be careful because the need extra space for the tempdb right?
– cpiock
Nov 21 '18 at 9:15
4
4
Best practice is definitely to have fast queries :) However in cases where this isn't possible, e.g. reporting or similar, possible solutions are to run the query when the database isn't busy, run the query against a copy of database, have copy functions which build the report data, maybe to another database. I would suggest that analysing every record in a table is probably not something you want to do on a live database.
– Dale Burrell
Nov 21 '18 at 9:18
Best practice is definitely to have fast queries :) However in cases where this isn't possible, e.g. reporting or similar, possible solutions are to run the query when the database isn't busy, run the query against a copy of database, have copy functions which build the report data, maybe to another database. I would suggest that analysing every record in a table is probably not something you want to do on a live database.
– Dale Burrell
Nov 21 '18 at 9:18
|
show 5 more comments
0
active
oldest
votes
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%2f53408089%2fis-long-running-select-locking-updates-on-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53408089%2fis-long-running-select-locking-updates-on-sql-server%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
3
Ideally you would improve the performance of your queries such that they are quick. However as a purely monitoring function you might decide you can get away without taking a shared lock using the query hint ` with (nolock)`. docs.microsoft.com/en-us/sql/t-sql/queries/…. I would not recommend that approach for you public facing site queries - they need to be improved.
– Dale Burrell
Nov 21 '18 at 9:08
1
Did you try the snapshot isolation for long running select queries?
– serge
Nov 21 '18 at 9:12
@DaleBurrell this means best practices to avoid locks of this kind is to have fast queries or to divide update statements and sql statements on different dbs?
– cpiock
Nov 21 '18 at 9:13
@serge i have seen that on snapshot isolation have to be careful because the need extra space for the tempdb right?
– cpiock
Nov 21 '18 at 9:15
4
Best practice is definitely to have fast queries :) However in cases where this isn't possible, e.g. reporting or similar, possible solutions are to run the query when the database isn't busy, run the query against a copy of database, have copy functions which build the report data, maybe to another database. I would suggest that analysing every record in a table is probably not something you want to do on a live database.
– Dale Burrell
Nov 21 '18 at 9:18