SQL Server – Will Deadlocks Occur When Deleting Rows While Adding Rows?

sql serversql-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

Best 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.

Related Question