Will deadlocks occur when deleting rows from a table that is being added to by another process?












2















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









share|improve this question









New contributor




LondonKiwi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

























    2















    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









    share|improve this question









    New contributor




    LondonKiwi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.























      2












      2








      2








      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









      share|improve this question









      New contributor




      LondonKiwi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.












      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






      share|improve this question









      New contributor




      LondonKiwi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      share|improve this question









      New contributor




      LondonKiwi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      share|improve this question




      share|improve this question








      edited Jan 7 at 16:48







      LondonKiwi













      New contributor




      LondonKiwi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked Jan 7 at 12:47









      LondonKiwiLondonKiwi

      133




      133




      New contributor




      LondonKiwi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      LondonKiwi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      LondonKiwi is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






















          1 Answer
          1






          active

          oldest

          votes


















          3















          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.






          share|improve this answer


























          • @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











          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
          });


          }
          });






          LondonKiwi is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          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









          3















          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.






          share|improve this answer


























          • @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
















          3















          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.






          share|improve this answer


























          • @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














          3












          3








          3








          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.






          share|improve this answer
















          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.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Jan 7 at 18:41

























          answered Jan 7 at 13:13









          jadarnel27jadarnel27

          3,9971330




          3,9971330













          • @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



















          • @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

















          @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










          LondonKiwi is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          LondonKiwi is a new contributor. Be nice, and check out our Code of Conduct.













          LondonKiwi is a new contributor. Be nice, and check out our Code of Conduct.












          LondonKiwi is a new contributor. Be nice, and check out our Code of Conduct.
















          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.




          draft saved


          draft discarded














          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





















































          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







          Popular posts from this blog

          How to make a Squid Proxy server?

          Is this a new Fibonacci Identity?

          Touch on Surface Book