Will (ROWLOCK,UPDLOCK,READPAST) with ORDER BY non-clustered columns work?
I have a SQL code as below
#table creation
CREATE TABLE TAB
([i_task_id] [int] IDENTITY(1,1) NOT NULL,
[i_priority] [tinyint] NULL,
[i_status] [smallint] NULL
CONSTRAINT [PK_TAB] PRIMARY KEY CLUSTERED
(
[i_task_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
#non-clustered index on i_status
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = 'IX_status' AND
OBJECT_ID = OBJECT_ID('TAB'))
BEGIN
CREATE NONCLUSTERED INDEX IX_status
ON [dbo].[TAB] (i_status)
END
#values in the table#
INSERT INTO TAB(i_priority,i_status)
VALUES (31,1),(30,1),(31,1),(20,1),(10,1)
#select query#
SELECT TOP 1 i_task_id
FROM TAB with (ROWLOCK,UPDLOCK,READPAST)
WHERE i_status = 1
ORDER BY i_priority desc,i_task_id asc
Locking does not work while executing the above query in 4 different sessions. I get result in one session where as nothing is returned in other 3 sessions.
When i tried to change the ORDER BY condition as below
ORDER BY i_task_id asc,i_priority desc
then the query worked and returned 4 different results in 4 sessions.
My requirement is if several users try to access the same select query at the same time, there should not be any deadlock situation(i.e each user should be provided the unlocked row).Also,each user should be given different task id in different sessions.
Will the locking with order by non-clustered columns work?
Thanks for your time!
sql-server locking deadlock
asked Nov 12 '18 at 12:13
nikithanikitha
61
add a comment |
I have a SQL code as below
#table creation
CREATE TABLE TAB
([i_task_id] [int] IDENTITY(1,1) NOT NULL,
[i_priority] [tinyint] NULL,
[i_status] [smallint] NULL
CONSTRAINT [PK_TAB] PRIMARY KEY CLUSTERED
(
[i_task_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
#non-clustered index on i_status
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = 'IX_status' AND
OBJECT_ID = OBJECT_ID('TAB'))
BEGIN
CREATE NONCLUSTERED INDEX IX_status
ON [dbo].[TAB] (i_status)
END
#values in the table#
INSERT INTO TAB(i_priority,i_status)
VALUES (31,1),(30,1),(31,1),(20,1),(10,1)
#select query#
SELECT TOP 1 i_task_id
FROM TAB with (ROWLOCK,UPDLOCK,READPAST)
WHERE i_status = 1
ORDER BY i_priority desc,i_task_id asc
Locking does not work while executing the above query in 4 different sessions. I get result in one session where as nothing is returned in other 3 sessions.
When i tried to change the ORDER BY condition as below
ORDER BY i_task_id asc,i_priority desc
then the query worked and returned 4 different results in 4 sessions.
My requirement is if several users try to access the same select query at the same time, there should not be any deadlock situation(i.e each user should be provided the unlocked row).Also,each user should be given different task id in different sessions.
Will the locking with order by non-clustered columns work?
Thanks for your time!
sql-server locking deadlock
asked Nov 12 '18 at 12:13
nikithanikitha
61
Does each session then update the status to something when it starts? How about when it's done? How is each session holding those locks? Can you show the transaction parts of the code too? Are you sending thei_task_idto the application and handling transaction logic there?
– Aaron Bertrand♦
Nov 12 '18 at 12:28
It's not about clustered-v-nonclustered, it's about your indexes not covering the "order by i_priority" ordering and thus causing a full index scan which in turn causes the whole table to get locked until the end of transaction.
– sharptooth
Nov 12 '18 at 14:39
add a comment |
I have a SQL code as below
#table creation
CREATE TABLE TAB
([i_task_id] [int] IDENTITY(1,1) NOT NULL,
[i_priority] [tinyint] NULL,
[i_status] [smallint] NULL
CONSTRAINT [PK_TAB] PRIMARY KEY CLUSTERED
(
[i_task_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
#non-clustered index on i_status
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = 'IX_status' AND
OBJECT_ID = OBJECT_ID('TAB'))
BEGIN
CREATE NONCLUSTERED INDEX IX_status
ON [dbo].[TAB] (i_status)
END
#values in the table#
INSERT INTO TAB(i_priority,i_status)
VALUES (31,1),(30,1),(31,1),(20,1),(10,1)
#select query#
SELECT TOP 1 i_task_id
FROM TAB with (ROWLOCK,UPDLOCK,READPAST)
WHERE i_status = 1
ORDER BY i_priority desc,i_task_id asc
Locking does not work while executing the above query in 4 different sessions. I get result in one session where as nothing is returned in other 3 sessions.
When i tried to change the ORDER BY condition as below
ORDER BY i_task_id asc,i_priority desc
then the query worked and returned 4 different results in 4 sessions.
My requirement is if several users try to access the same select query at the same time, there should not be any deadlock situation(i.e each user should be provided the unlocked row).Also,each user should be given different task id in different sessions.
Will the locking with order by non-clustered columns work?
Thanks for your time!
sql-server locking deadlock
asked Nov 12 '18 at 12:13
nikithanikitha
61
I have a SQL code as below
#table creation
CREATE TABLE TAB
([i_task_id] [int] IDENTITY(1,1) NOT NULL,
[i_priority] [tinyint] NULL,
[i_status] [smallint] NULL
CONSTRAINT [PK_TAB] PRIMARY KEY CLUSTERED
(
[i_task_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
#non-clustered index on i_status
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = 'IX_status' AND
OBJECT_ID = OBJECT_ID('TAB'))
BEGIN
CREATE NONCLUSTERED INDEX IX_status
ON [dbo].[TAB] (i_status)
END
#values in the table#
INSERT INTO TAB(i_priority,i_status)
VALUES (31,1),(30,1),(31,1),(20,1),(10,1)
#select query#
SELECT TOP 1 i_task_id
FROM TAB with (ROWLOCK,UPDLOCK,READPAST)
WHERE i_status = 1
ORDER BY i_priority desc,i_task_id asc
Locking does not work while executing the above query in 4 different sessions. I get result in one session where as nothing is returned in other 3 sessions.
When i tried to change the ORDER BY condition as below
ORDER BY i_task_id asc,i_priority desc
then the query worked and returned 4 different results in 4 sessions.
My requirement is if several users try to access the same select query at the same time, there should not be any deadlock situation(i.e each user should be provided the unlocked row).Also,each user should be given different task id in different sessions.
Will the locking with order by non-clustered columns work?
Thanks for your time!
sql-server locking deadlock
sql-server locking deadlock
asked Nov 12 '18 at 12:13
nikithanikitha
61
asked Nov 12 '18 at 12:13
nikithanikitha
61
asked Nov 12 '18 at 12:13
nikithanikitha
61
asked Nov 12 '18 at 12:13
nikithanikitha
61
asked Nov 12 '18 at 12:13
nikithanikitha
61
61
Does each session then update the status to something when it starts? How about when it's done? How is each session holding those locks? Can you show the transaction parts of the code too? Are you sending thei_task_idto the application and handling transaction logic there?
– Aaron Bertrand♦
Nov 12 '18 at 12:28
It's not about clustered-v-nonclustered, it's about your indexes not covering the "order by i_priority" ordering and thus causing a full index scan which in turn causes the whole table to get locked until the end of transaction.
– sharptooth
Nov 12 '18 at 14:39
add a comment |
Does each session then update the status to something when it starts? How about when it's done? How is each session holding those locks? Can you show the transaction parts of the code too? Are you sending thei_task_idto the application and handling transaction logic there?
– Aaron Bertrand♦
Nov 12 '18 at 12:28
It's not about clustered-v-nonclustered, it's about your indexes not covering the "order by i_priority" ordering and thus causing a full index scan which in turn causes the whole table to get locked until the end of transaction.
– sharptooth
Nov 12 '18 at 14:39
Does each session then update the status to something when it starts? How about when it's done? How is each session holding those locks? Can you show the transaction parts of the code too? Are you sending the
i_task_id to the application and handling transaction logic there?– Aaron Bertrand♦
Nov 12 '18 at 12:28
Does each session then update the status to something when it starts? How about when it's done? How is each session holding those locks? Can you show the transaction parts of the code too? Are you sending the
i_task_id to the application and handling transaction logic there?– Aaron Bertrand♦
Nov 12 '18 at 12:28
It's not about clustered-v-nonclustered, it's about your indexes not covering the "order by i_priority" ordering and thus causing a full index scan which in turn causes the whole table to get locked until the end of transaction.
– sharptooth
Nov 12 '18 at 14:39
It's not about clustered-v-nonclustered, it's about your indexes not covering the "order by i_priority" ordering and thus causing a full index scan which in turn causes the whole table to get locked until the end of transaction.
– sharptooth
Nov 12 '18 at 14:39
add a comment |
1 Answer
1
active
oldest
votes
I would opt for marking the row as "in process" (e.g. having a status for the equivalent of "checked out") as opposed to relying solely on row locking:
;WITH x AS
(
SELECT TOP (1) i_task_id, i_status
FROM dbo.TAB WITH (ROWLOCK,UPDLOCK,READPAST)
WHERE i_status = 1
ORDER BY i_priority DESC,i_task_id ASC
)
UPDATE x SET i_status = 2 -- in process? locked?
OUTPUT inserted.i_task_id
WHERE i_status = 1;
No blocking or deadlocking in my tests. The 6th session won't block or deadlock either, it will just return no rows.
answered Nov 12 '18 at 12:33
Aaron Bertrand♦Aaron Bertrand
150k18284482
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f222334%2fwill-rowlock-updlock-readpast-with-order-by-non-clustered-columns-work%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I would opt for marking the row as "in process" (e.g. having a status for the equivalent of "checked out") as opposed to relying solely on row locking:
;WITH x AS
(
SELECT TOP (1) i_task_id, i_status
FROM dbo.TAB WITH (ROWLOCK,UPDLOCK,READPAST)
WHERE i_status = 1
ORDER BY i_priority DESC,i_task_id ASC
)
UPDATE x SET i_status = 2 -- in process? locked?
OUTPUT inserted.i_task_id
WHERE i_status = 1;
No blocking or deadlocking in my tests. The 6th session won't block or deadlock either, it will just return no rows.
answered Nov 12 '18 at 12:33
Aaron Bertrand♦Aaron Bertrand
150k18284482
add a comment |
I would opt for marking the row as "in process" (e.g. having a status for the equivalent of "checked out") as opposed to relying solely on row locking:
;WITH x AS
(
SELECT TOP (1) i_task_id, i_status
FROM dbo.TAB WITH (ROWLOCK,UPDLOCK,READPAST)
WHERE i_status = 1
ORDER BY i_priority DESC,i_task_id ASC
)
UPDATE x SET i_status = 2 -- in process? locked?
OUTPUT inserted.i_task_id
WHERE i_status = 1;
No blocking or deadlocking in my tests. The 6th session won't block or deadlock either, it will just return no rows.
answered Nov 12 '18 at 12:33
Aaron Bertrand♦Aaron Bertrand
150k18284482
add a comment |
I would opt for marking the row as "in process" (e.g. having a status for the equivalent of "checked out") as opposed to relying solely on row locking:
;WITH x AS
(
SELECT TOP (1) i_task_id, i_status
FROM dbo.TAB WITH (ROWLOCK,UPDLOCK,READPAST)
WHERE i_status = 1
ORDER BY i_priority DESC,i_task_id ASC
)
UPDATE x SET i_status = 2 -- in process? locked?
OUTPUT inserted.i_task_id
WHERE i_status = 1;
No blocking or deadlocking in my tests. The 6th session won't block or deadlock either, it will just return no rows.
answered Nov 12 '18 at 12:33
Aaron Bertrand♦Aaron Bertrand
150k18284482
I would opt for marking the row as "in process" (e.g. having a status for the equivalent of "checked out") as opposed to relying solely on row locking:
;WITH x AS
(
SELECT TOP (1) i_task_id, i_status
FROM dbo.TAB WITH (ROWLOCK,UPDLOCK,READPAST)
WHERE i_status = 1
ORDER BY i_priority DESC,i_task_id ASC
)
UPDATE x SET i_status = 2 -- in process? locked?
OUTPUT inserted.i_task_id
WHERE i_status = 1;
No blocking or deadlocking in my tests. The 6th session won't block or deadlock either, it will just return no rows.
answered Nov 12 '18 at 12:33
Aaron Bertrand♦Aaron Bertrand
150k18284482
answered Nov 12 '18 at 12:33
Aaron Bertrand♦Aaron Bertrand
150k18284482
answered Nov 12 '18 at 12:33
Aaron Bertrand♦Aaron Bertrand
150k18284482
answered Nov 12 '18 at 12:33
Aaron Bertrand♦Aaron Bertrand
150k18284482
150k18284482
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f222334%2fwill-rowlock-updlock-readpast-with-order-by-non-clustered-columns-work%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
Does each session then update the status to something when it starts? How about when it's done? How is each session holding those locks? Can you show the transaction parts of the code too? Are you sending the
i_task_idto the application and handling transaction logic there?– Aaron Bertrand♦
Nov 12 '18 at 12:28
It's not about clustered-v-nonclustered, it's about your indexes not covering the "order by i_priority" ordering and thus causing a full index scan which in turn causes the whole table to get locked until the end of transaction.
– sharptooth
Nov 12 '18 at 14:39