Will deadlocks occur when deleting rows from a table that is being added to by another process?
I have to archive data from a table into another table whilst the original table is in use. The archival process is to copy the rows into another table and then delete the rows from the original table. The rows to be archived are selected by a column that has the Identity property.
A factor is that the table from which the rows are being archived will be in use - new rows will be written into the table as the archival process is running. This is a 24/7 application that cannot be stopped.
The rows that are being deleted are at the low end of the Identity column numbering whilst the new rows will be being added at the end of the table.
My concern is deadlocks. Is there a chance that deadlocks could occur between the 2 processes?
The archival process will be run from a Stored Procedure? Should I set the DEADLOCK_PRIORITY to LOW to ensure that if a deadlock does occur that the archival process will be killed and not the running application?
Should I also set TRANSACTION ISOLATION LEVEL?
Thanks
Table Definition:
CREATE TABLE [dbo].[DummyName](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Payment Reference] [nvarchar](18) NULL DEFAULT (N''),
[Payment Amount] [decimal](28, 10) NULL,
[Payment Date] [datetime] NULL,
[Payer ID] [nvarchar](34) NULL DEFAULT (N''),
[Payer Account] [nvarchar](174) NULL DEFAULT (N''),
[Payer Name] [nvarchar](174) NULL DEFAULT (N''),
[Payer Type] [nvarchar](35) NULL DEFAULT (N''),
[Bank Reference] [nvarchar](16) NULL DEFAULT (N''),
...
Index Definition:
ALTER TABLE [dbo].[DummyName] ADD PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
sql-server sql-server-2012
add a comment |
I have to archive data from a table into another table whilst the original table is in use. The archival process is to copy the rows into another table and then delete the rows from the original table. The rows to be archived are selected by a column that has the Identity property.
A factor is that the table from which the rows are being archived will be in use - new rows will be written into the table as the archival process is running. This is a 24/7 application that cannot be stopped.
The rows that are being deleted are at the low end of the Identity column numbering whilst the new rows will be being added at the end of the table.
My concern is deadlocks. Is there a chance that deadlocks could occur between the 2 processes?
The archival process will be run from a Stored Procedure? Should I set the DEADLOCK_PRIORITY to LOW to ensure that if a deadlock does occur that the archival process will be killed and not the running application?
Should I also set TRANSACTION ISOLATION LEVEL?
Thanks
Table Definition:
CREATE TABLE [dbo].[DummyName](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Payment Reference] [nvarchar](18) NULL DEFAULT (N''),
[Payment Amount] [decimal](28, 10) NULL,
[Payment Date] [datetime] NULL,
[Payer ID] [nvarchar](34) NULL DEFAULT (N''),
[Payer Account] [nvarchar](174) NULL DEFAULT (N''),
[Payer Name] [nvarchar](174) NULL DEFAULT (N''),
[Payer Type] [nvarchar](35) NULL DEFAULT (N''),
[Bank Reference] [nvarchar](16) NULL DEFAULT (N''),
...
Index Definition:
ALTER TABLE [dbo].[DummyName] ADD PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
sql-server sql-server-2012
add a comment |
I have to archive data from a table into another table whilst the original table is in use. The archival process is to copy the rows into another table and then delete the rows from the original table. The rows to be archived are selected by a column that has the Identity property.
A factor is that the table from which the rows are being archived will be in use - new rows will be written into the table as the archival process is running. This is a 24/7 application that cannot be stopped.
The rows that are being deleted are at the low end of the Identity column numbering whilst the new rows will be being added at the end of the table.
My concern is deadlocks. Is there a chance that deadlocks could occur between the 2 processes?
The archival process will be run from a Stored Procedure? Should I set the DEADLOCK_PRIORITY to LOW to ensure that if a deadlock does occur that the archival process will be killed and not the running application?
Should I also set TRANSACTION ISOLATION LEVEL?
Thanks
Table Definition:
CREATE TABLE [dbo].[DummyName](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Payment Reference] [nvarchar](18) NULL DEFAULT (N''),
[Payment Amount] [decimal](28, 10) NULL,
[Payment Date] [datetime] NULL,
[Payer ID] [nvarchar](34) NULL DEFAULT (N''),
[Payer Account] [nvarchar](174) NULL DEFAULT (N''),
[Payer Name] [nvarchar](174) NULL DEFAULT (N''),
[Payer Type] [nvarchar](35) NULL DEFAULT (N''),
[Bank Reference] [nvarchar](16) NULL DEFAULT (N''),
...
Index Definition:
ALTER TABLE [dbo].[DummyName] ADD PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
sql-server sql-server-2012
I have to archive data from a table into another table whilst the original table is in use. The archival process is to copy the rows into another table and then delete the rows from the original table. The rows to be archived are selected by a column that has the Identity property.
A factor is that the table from which the rows are being archived will be in use - new rows will be written into the table as the archival process is running. This is a 24/7 application that cannot be stopped.
The rows that are being deleted are at the low end of the Identity column numbering whilst the new rows will be being added at the end of the table.
My concern is deadlocks. Is there a chance that deadlocks could occur between the 2 processes?
The archival process will be run from a Stored Procedure? Should I set the DEADLOCK_PRIORITY to LOW to ensure that if a deadlock does occur that the archival process will be killed and not the running application?
Should I also set TRANSACTION ISOLATION LEVEL?
Thanks
Table Definition:
CREATE TABLE [dbo].[DummyName](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Payment Reference] [nvarchar](18) NULL DEFAULT (N''),
[Payment Amount] [decimal](28, 10) NULL,
[Payment Date] [datetime] NULL,
[Payer ID] [nvarchar](34) NULL DEFAULT (N''),
[Payer Account] [nvarchar](174) NULL DEFAULT (N''),
[Payer Name] [nvarchar](174) NULL DEFAULT (N''),
[Payer Type] [nvarchar](35) NULL DEFAULT (N''),
[Bank Reference] [nvarchar](16) NULL DEFAULT (N''),
...
Index Definition:
ALTER TABLE [dbo].[DummyName] ADD PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
sql-server sql-server-2012
sql-server sql-server-2012
edited Jan 7 at 16:48
LondonKiwi
asked Jan 7 at 12:47
LondonKiwiLondonKiwi
304
304
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The rows to be archived are selected by a column that has the Identity property
In this scenario, you're inserting rows at the end of the index, and deleting rows from the beginning. So there shouldn't be overlapping locks that would lead to deadlocks.
This assumes that your archive process won't ever run concurrently. Multiple concurrent archive operations could conceivably deadlock with each other, since they'll be operating on the same range of rows. You should make sure that doesn't happen by, for instance, scheduling this as a SQL Server Agent job.
Just make sure not to delete too many rows at once from the "live" table after archiving them, as this could lead to lock escalation (which sounds like it would be undesirable in your situation). Check out Michael Swart's excellent article about that topic of doing batch processing in a way that promotes performance and concurrency: Take Care When Scripting Batches
Even though your main table is a heap, you should still be okay with regards to deadlocks, as all your queries will tend to seek on the nonclustered PK and delete using the RID from there. It seems possible that you could experience page level blocking (or deadlocks) within the heap itself, depending on a lot of factors, but it would be hard to know for sure without seeing the workload in action.
Should I set the DEADLOCK_PRIORITY to LOW to ensure that if a deadlock does occur that the archival process will be killed and not the running application?
Yes, that's a reasonable step to take. And as you mentioned is a good way to make sure the archive workload is re-prioritized with regards to concurrency.
Should I also set TRANSACTION ISOLATION LEVEL?
The default isolation level (READ COMMITTED
) should be okay in this situation.
@LondonKiwi Thanks! As long as yourSELECT
andDELETE
are on the Id column, all the operations should seek on the nonclustered PK and avoid blocking / deadlocks. Is there a reason that you are leaving this table a heap?
– jadarnel27
Jan 7 at 18:24
The database is part of a third-party software package that we use. Its how it was delivered. I am loathe to change it as I said its part of a 24/7 system we use.
– LondonKiwi
Jan 8 at 8:19
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%2f226500%2fwill-deadlocks-occur-when-deleting-rows-from-a-table-that-is-being-added-to-by-a%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
The rows to be archived are selected by a column that has the Identity property
In this scenario, you're inserting rows at the end of the index, and deleting rows from the beginning. So there shouldn't be overlapping locks that would lead to deadlocks.
This assumes that your archive process won't ever run concurrently. Multiple concurrent archive operations could conceivably deadlock with each other, since they'll be operating on the same range of rows. You should make sure that doesn't happen by, for instance, scheduling this as a SQL Server Agent job.
Just make sure not to delete too many rows at once from the "live" table after archiving them, as this could lead to lock escalation (which sounds like it would be undesirable in your situation). Check out Michael Swart's excellent article about that topic of doing batch processing in a way that promotes performance and concurrency: Take Care When Scripting Batches
Even though your main table is a heap, you should still be okay with regards to deadlocks, as all your queries will tend to seek on the nonclustered PK and delete using the RID from there. It seems possible that you could experience page level blocking (or deadlocks) within the heap itself, depending on a lot of factors, but it would be hard to know for sure without seeing the workload in action.
Should I set the DEADLOCK_PRIORITY to LOW to ensure that if a deadlock does occur that the archival process will be killed and not the running application?
Yes, that's a reasonable step to take. And as you mentioned is a good way to make sure the archive workload is re-prioritized with regards to concurrency.
Should I also set TRANSACTION ISOLATION LEVEL?
The default isolation level (READ COMMITTED
) should be okay in this situation.
@LondonKiwi Thanks! As long as yourSELECT
andDELETE
are on the Id column, all the operations should seek on the nonclustered PK and avoid blocking / deadlocks. Is there a reason that you are leaving this table a heap?
– jadarnel27
Jan 7 at 18:24
The database is part of a third-party software package that we use. Its how it was delivered. I am loathe to change it as I said its part of a 24/7 system we use.
– LondonKiwi
Jan 8 at 8:19
add a comment |
The rows to be archived are selected by a column that has the Identity property
In this scenario, you're inserting rows at the end of the index, and deleting rows from the beginning. So there shouldn't be overlapping locks that would lead to deadlocks.
This assumes that your archive process won't ever run concurrently. Multiple concurrent archive operations could conceivably deadlock with each other, since they'll be operating on the same range of rows. You should make sure that doesn't happen by, for instance, scheduling this as a SQL Server Agent job.
Just make sure not to delete too many rows at once from the "live" table after archiving them, as this could lead to lock escalation (which sounds like it would be undesirable in your situation). Check out Michael Swart's excellent article about that topic of doing batch processing in a way that promotes performance and concurrency: Take Care When Scripting Batches
Even though your main table is a heap, you should still be okay with regards to deadlocks, as all your queries will tend to seek on the nonclustered PK and delete using the RID from there. It seems possible that you could experience page level blocking (or deadlocks) within the heap itself, depending on a lot of factors, but it would be hard to know for sure without seeing the workload in action.
Should I set the DEADLOCK_PRIORITY to LOW to ensure that if a deadlock does occur that the archival process will be killed and not the running application?
Yes, that's a reasonable step to take. And as you mentioned is a good way to make sure the archive workload is re-prioritized with regards to concurrency.
Should I also set TRANSACTION ISOLATION LEVEL?
The default isolation level (READ COMMITTED
) should be okay in this situation.
@LondonKiwi Thanks! As long as yourSELECT
andDELETE
are on the Id column, all the operations should seek on the nonclustered PK and avoid blocking / deadlocks. Is there a reason that you are leaving this table a heap?
– jadarnel27
Jan 7 at 18:24
The database is part of a third-party software package that we use. Its how it was delivered. I am loathe to change it as I said its part of a 24/7 system we use.
– LondonKiwi
Jan 8 at 8:19
add a comment |
The rows to be archived are selected by a column that has the Identity property
In this scenario, you're inserting rows at the end of the index, and deleting rows from the beginning. So there shouldn't be overlapping locks that would lead to deadlocks.
This assumes that your archive process won't ever run concurrently. Multiple concurrent archive operations could conceivably deadlock with each other, since they'll be operating on the same range of rows. You should make sure that doesn't happen by, for instance, scheduling this as a SQL Server Agent job.
Just make sure not to delete too many rows at once from the "live" table after archiving them, as this could lead to lock escalation (which sounds like it would be undesirable in your situation). Check out Michael Swart's excellent article about that topic of doing batch processing in a way that promotes performance and concurrency: Take Care When Scripting Batches
Even though your main table is a heap, you should still be okay with regards to deadlocks, as all your queries will tend to seek on the nonclustered PK and delete using the RID from there. It seems possible that you could experience page level blocking (or deadlocks) within the heap itself, depending on a lot of factors, but it would be hard to know for sure without seeing the workload in action.
Should I set the DEADLOCK_PRIORITY to LOW to ensure that if a deadlock does occur that the archival process will be killed and not the running application?
Yes, that's a reasonable step to take. And as you mentioned is a good way to make sure the archive workload is re-prioritized with regards to concurrency.
Should I also set TRANSACTION ISOLATION LEVEL?
The default isolation level (READ COMMITTED
) should be okay in this situation.
The rows to be archived are selected by a column that has the Identity property
In this scenario, you're inserting rows at the end of the index, and deleting rows from the beginning. So there shouldn't be overlapping locks that would lead to deadlocks.
This assumes that your archive process won't ever run concurrently. Multiple concurrent archive operations could conceivably deadlock with each other, since they'll be operating on the same range of rows. You should make sure that doesn't happen by, for instance, scheduling this as a SQL Server Agent job.
Just make sure not to delete too many rows at once from the "live" table after archiving them, as this could lead to lock escalation (which sounds like it would be undesirable in your situation). Check out Michael Swart's excellent article about that topic of doing batch processing in a way that promotes performance and concurrency: Take Care When Scripting Batches
Even though your main table is a heap, you should still be okay with regards to deadlocks, as all your queries will tend to seek on the nonclustered PK and delete using the RID from there. It seems possible that you could experience page level blocking (or deadlocks) within the heap itself, depending on a lot of factors, but it would be hard to know for sure without seeing the workload in action.
Should I set the DEADLOCK_PRIORITY to LOW to ensure that if a deadlock does occur that the archival process will be killed and not the running application?
Yes, that's a reasonable step to take. And as you mentioned is a good way to make sure the archive workload is re-prioritized with regards to concurrency.
Should I also set TRANSACTION ISOLATION LEVEL?
The default isolation level (READ COMMITTED
) should be okay in this situation.
edited Jan 7 at 18:41
answered Jan 7 at 13:13
jadarnel27jadarnel27
5,03411736
5,03411736
@LondonKiwi Thanks! As long as yourSELECT
andDELETE
are on the Id column, all the operations should seek on the nonclustered PK and avoid blocking / deadlocks. Is there a reason that you are leaving this table a heap?
– jadarnel27
Jan 7 at 18:24
The database is part of a third-party software package that we use. Its how it was delivered. I am loathe to change it as I said its part of a 24/7 system we use.
– LondonKiwi
Jan 8 at 8:19
add a comment |
@LondonKiwi Thanks! As long as yourSELECT
andDELETE
are on the Id column, all the operations should seek on the nonclustered PK and avoid blocking / deadlocks. Is there a reason that you are leaving this table a heap?
– jadarnel27
Jan 7 at 18:24
The database is part of a third-party software package that we use. Its how it was delivered. I am loathe to change it as I said its part of a 24/7 system we use.
– LondonKiwi
Jan 8 at 8:19
@LondonKiwi Thanks! As long as your
SELECT
and DELETE
are on the Id column, all the operations should seek on the nonclustered PK and avoid blocking / deadlocks. Is there a reason that you are leaving this table a heap?– jadarnel27
Jan 7 at 18:24
@LondonKiwi Thanks! As long as your
SELECT
and DELETE
are on the Id column, all the operations should seek on the nonclustered PK and avoid blocking / deadlocks. Is there a reason that you are leaving this table a heap?– jadarnel27
Jan 7 at 18:24
The database is part of a third-party software package that we use. Its how it was delivered. I am loathe to change it as I said its part of a 24/7 system we use.
– LondonKiwi
Jan 8 at 8:19
The database is part of a third-party software package that we use. Its how it was delivered. I am loathe to change it as I said its part of a 24/7 system we use.
– LondonKiwi
Jan 8 at 8:19
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%2f226500%2fwill-deadlocks-occur-when-deleting-rows-from-a-table-that-is-being-added-to-by-a%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