CODEWITHSUNDEEP
sqlsql-serverdatabase-deadlocks
KDH21
KDH21

Reputation: 11

Table deadlock issues with lock escalations

I have a table where anywhere between 1 and 5 million records come in as a batch and then a bunch of stored procedures are ran on them that update and delete records in the batch. All of these stored procedures are using two fields for selectivity so they only run on the records in that batch. Both of these fields are in a nonclustered index. There are times when multiple batches are run at the same time and I am continuously getting deadlocks happening between batches, I assume due to lock escalations. Trying to figure out if there is a way to solve this without a complete redesign to use a dedicated table for each batch. Is disabling page locks asking for more trouble?

Additional information:

Example of table structure and Index(the real one has a lot more columns than this)

CREATE TABLE [dbo].[TempImport](
    [UID] [int] IDENTITY(1,1) NOT NULL,
    [EID] [int] NULL,
    [EXTID] [int] NULL,
    [COL1] [varchar](50) NULL,
    [COL2] [varchar](50) NULL,
 CONSTRAINT [PK_TempImport] PRIMARY KEY CLUSTERED 
(
    [UID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_TempImport_Main] ON [dbo].[TempImport]
(
    [EID] ASC,
    [EXTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

And the types of queries in the stored procedures look something like this:

update TempImport set COL1 = 'foo' where EID = @EID and EXTID = @EXT and COL2='bar'

And the last thing that happens when batch completes is something like this:

Delete from TempImport where EID = @EID and EXTID = @EXT

It is typically the delete and the updates in the stored procedures that are involved in the deadlock.

Please let me know if any other info would be useful

Upvotes: 1

Views: 525

Answers (1)

seanb
seanb

Reputation: 6685

Just some potential suggestions

  • Yeah, you could split them up into (say) batches of 2000, to stop the row locks escalating to table locks, but you'd have a gazillion running. Probably not a good solution.
  • You could modify the update processes (as per Brent Ozar's video re deadlocks I recommended in the comments) to update each table once and once only. As a suggestion, you could also try encapsulating them in transactions. These could remove deadlocks, but add blocking (where the second operation has to wait for the first to finish).
  • A structural method is to make a 'loading' or 'scratch' table which has IDs and relevant data to be operated on (you could see this as a queue). When calls to do updates come in, they simply insert their requests into that queue. Then you have an asynchronous process (that can only be called one at a time) that gets all outstanding data from the queue (and flags it as such), does the relevant processing, then cleans out the processed data from the scratch table.

Note that if you have other things accessing/using this table, then they could get blocked or deadlocked on this table too, and then your approach needs to be very careful.

Upvotes: 0

Related Questions