Why isn't block the row?
I would like to block a row, but I would like to block to avoid to be update while I am using it, but I would like to let select the row. So I think that a UPDLOCK it would be a good option instead of serializable isolation level because it would block the reads too. So I am trying to use this query to test:
begin tran
select ID from MyTable with(UPLDOCK);
rollback
I run this query in one SSMS and in a second SSMS, but in the second SMSS is not blocked.
If I use a select * then it is blocked, but I would like to block when I get only the fields that I need.
So my question is, would it be possible to block the row only for updates and deletes (like UPDLOCK does) but getting only the fields that I need?
Thanks.
sql-server edited Dec 30 '18 at 12:36
marc_s
572k12811041252
asked Dec 28 '18 at 9:34
Álvaro GarcíaÁlvaro García
6,3111861116
add a comment |
I would like to block a row, but I would like to block to avoid to be update while I am using it, but I would like to let select the row. So I think that a UPDLOCK it would be a good option instead of serializable isolation level because it would block the reads too. So I am trying to use this query to test:
begin tran
select ID from MyTable with(UPLDOCK);
rollback
I run this query in one SSMS and in a second SSMS, but in the second SMSS is not blocked.
If I use a select * then it is blocked, but I would like to block when I get only the fields that I need.
So my question is, would it be possible to block the row only for updates and deletes (like UPDLOCK does) but getting only the fields that I need?
Thanks.
sql-server edited Dec 30 '18 at 12:36
marc_s
572k12811041252
asked Dec 28 '18 at 9:34
Álvaro GarcíaÁlvaro García
6,3111861116
If the second query usesupdlocktoo, it will be blocked.
– GSerg
Dec 28 '18 at 9:39
For me it works fine using "Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64) Sep 23 2016 18:13:56 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) ". So, it blocks the rows even if I select only ID and try in another window to run the UPDATE before to run the ROLLBACK line in the main window.
– Angel M.
Dec 28 '18 at 9:45
Why? If this is to generate an auto id, either use an identity or a sequence.
– Nick.McDermaid
Dec 28 '18 at 10:05
add a comment |
I would like to block a row, but I would like to block to avoid to be update while I am using it, but I would like to let select the row. So I think that a UPDLOCK it would be a good option instead of serializable isolation level because it would block the reads too. So I am trying to use this query to test:
begin tran
select ID from MyTable with(UPLDOCK);
rollback
I run this query in one SSMS and in a second SSMS, but in the second SMSS is not blocked.
If I use a select * then it is blocked, but I would like to block when I get only the fields that I need.
So my question is, would it be possible to block the row only for updates and deletes (like UPDLOCK does) but getting only the fields that I need?
Thanks.
sql-server edited Dec 30 '18 at 12:36
marc_s
572k12811041252
asked Dec 28 '18 at 9:34
Álvaro GarcíaÁlvaro García
6,3111861116
I would like to block a row, but I would like to block to avoid to be update while I am using it, but I would like to let select the row. So I think that a UPDLOCK it would be a good option instead of serializable isolation level because it would block the reads too. So I am trying to use this query to test:
begin tran
select ID from MyTable with(UPLDOCK);
rollback
I run this query in one SSMS and in a second SSMS, but in the second SMSS is not blocked.
If I use a select * then it is blocked, but I would like to block when I get only the fields that I need.
So my question is, would it be possible to block the row only for updates and deletes (like UPDLOCK does) but getting only the fields that I need?
Thanks.
sql-server sql-server edited Dec 30 '18 at 12:36
marc_s
572k12811041252
asked Dec 28 '18 at 9:34
Álvaro GarcíaÁlvaro García
6,3111861116
edited Dec 30 '18 at 12:36
marc_s
572k12811041252
asked Dec 28 '18 at 9:34
Álvaro GarcíaÁlvaro García
6,3111861116
edited Dec 30 '18 at 12:36
marc_s
572k12811041252
edited Dec 30 '18 at 12:36
marc_s
572k12811041252
edited Dec 30 '18 at 12:36
marc_s
572k12811041252
572k12811041252
asked Dec 28 '18 at 9:34
Álvaro GarcíaÁlvaro García
6,3111861116
asked Dec 28 '18 at 9:34
Álvaro GarcíaÁlvaro García
6,3111861116
asked Dec 28 '18 at 9:34
Álvaro GarcíaÁlvaro García
6,3111861116
6,3111861116
If the second query usesupdlocktoo, it will be blocked.
– GSerg
Dec 28 '18 at 9:39
For me it works fine using "Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64) Sep 23 2016 18:13:56 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) ". So, it blocks the rows even if I select only ID and try in another window to run the UPDATE before to run the ROLLBACK line in the main window.
– Angel M.
Dec 28 '18 at 9:45
Why? If this is to generate an auto id, either use an identity or a sequence.
– Nick.McDermaid
Dec 28 '18 at 10:05
add a comment |
If the second query usesupdlocktoo, it will be blocked.
– GSerg
Dec 28 '18 at 9:39
For me it works fine using "Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64) Sep 23 2016 18:13:56 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) ". So, it blocks the rows even if I select only ID and try in another window to run the UPDATE before to run the ROLLBACK line in the main window.
– Angel M.
Dec 28 '18 at 9:45
Why? If this is to generate an auto id, either use an identity or a sequence.
– Nick.McDermaid
Dec 28 '18 at 10:05
If the second query uses
updlock too, it will be blocked.– GSerg
Dec 28 '18 at 9:39
If the second query uses
updlock too, it will be blocked.– GSerg
Dec 28 '18 at 9:39
For me it works fine using "Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64) Sep 23 2016 18:13:56 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) ". So, it blocks the rows even if I select only ID and try in another window to run the UPDATE before to run the ROLLBACK line in the main window.
– Angel M.
Dec 28 '18 at 9:45
For me it works fine using "Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64) Sep 23 2016 18:13:56 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) ". So, it blocks the rows even if I select only ID and try in another window to run the UPDATE before to run the ROLLBACK line in the main window.
– Angel M.
Dec 28 '18 at 9:45
Why? If this is to generate an auto id, either use an identity or a sequence.
– Nick.McDermaid
Dec 28 '18 at 10:05
Why? If this is to generate an auto id, either use an identity or a sequence.
– Nick.McDermaid
Dec 28 '18 at 10:05
add a comment |
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%2f53956360%2fwhy-isnt-block-the-row%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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53956360%2fwhy-isnt-block-the-row%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
If the second query uses
updlocktoo, it will be blocked.– GSerg
Dec 28 '18 at 9:39
For me it works fine using "Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64) Sep 23 2016 18:13:56 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) ". So, it blocks the rows even if I select only ID and try in another window to run the UPDATE before to run the ROLLBACK line in the main window.
– Angel M.
Dec 28 '18 at 9:45
Why? If this is to generate an auto id, either use an identity or a sequence.
– Nick.McDermaid
Dec 28 '18 at 10:05