What is causing this SQL 2005 Primary Key Deadlock between two real-time bulk upserts?
- by skimania
Here's the scenario:
I've got a table called MarketDataCurrent (MDC) that has live updating stock prices.
I've got one process called 'LiveFeed' which reads prices streaming from the wire, queues up inserts, and uses a 'bulk upload to temp table then insert/update to MDC table.' (BulkUpsert)
I've got another process which then reads this data, computes other data, and then saves the results back into the same table, using a similar BulkUpsert stored proc.
Thirdly, there are a multitude of users running a C# Gui polling the MDC table and reading updates from it.
Now, during the day when the data is changing rapidly, things run pretty smoothly, but then, after market hours, we've recently started seeing an increasing number of Deadlock exceptions coming out of the database, nowadays we see 10-20 a day. The imporant thing to note here is that these happen when the values are NOT changing.
Here's all the relevant info:
Table Def:
CREATE TABLE [dbo].[MarketDataCurrent](
[MDID] [int] NOT NULL,
[LastUpdate] [datetime] NOT NULL,
[Value] [float] NOT NULL,
[Source] [varchar](20) NULL,
CONSTRAINT [PK_MarketDataCurrent] PRIMARY KEY CLUSTERED
(
[MDID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-
stackoverflow wont let me post images until my reputation goes up to 10, so i'll add them as soon as you bump me up, hopefully as a result of this question.
![alt text][1]
[1]: http://farm5.static.flickr.com/4049/4690759452_6b94ff7b34.jpg
I've got a Sql Profiler Trace Running, catching the deadlocks, and here's what all the graphs look like.
stackoverflow wont let me post images until my reputation goes up to 10, so i'll add them as soon as you bump me up, hopefully as a result of this question.
![alt text][2]
[2]: http://farm5.static.flickr.com/4035/4690125231_78d84c9e15_b.jpg
Process 258 is called the following 'BulkUpsert' stored proc, repeatedly, while 73 is calling the next one:
ALTER proc [dbo].[MarketDataCurrent_BulkUpload]
@updateTime datetime,
@source varchar(10)
as
begin transaction
update c with (rowlock) set LastUpdate = getdate(), Value = t.Value, Source = @source
from MarketDataCurrent c INNER JOIN #MDTUP t ON c.MDID = t.mdid
where c.lastUpdate < @updateTime
and c.mdid not in (select mdid from MarketData where LiveFeedTicker is not null and PriceSource like 'LiveFeed.%')
and c.value <> t.value
insert into MarketDataCurrent
with (rowlock)
select MDID, getdate(), Value, @source from #MDTUP
where mdid not in (select mdid from MarketDataCurrent with (nolock))
and mdid not in (select mdid from MarketData where LiveFeedTicker is not null and PriceSource like 'LiveFeed.%')
commit
And the other one:
ALTER PROCEDURE [dbo].[MarketDataCurrent_LiveFeedUpload]
AS
begin transaction
-- Update existing mdid
UPDATE c WITH (ROWLOCK) SET LastUpdate = t.LastUpdate, Value = t.Value, Source = t.Source
FROM MarketDataCurrent c INNER JOIN #TEMPTABLE2 t ON c.MDID = t.mdid;
-- Insert new MDID
INSERT INTO MarketDataCurrent with (ROWLOCK) SELECT * FROM #TEMPTABLE2
WHERE MDID NOT IN (SELECT MDID FROM MarketDataCurrent with (NOLOCK))
-- Clean up the temp table
DELETE #TEMPTABLE2
commit
To clarify, those Temp Tables are being created by the C# code on the same connection and are populated using the C# SqlBulkCopy class.
To me it looks like it's deadlocking on the PK of the table, so I tried removing that PK and switching to a Unique Constraint instead but that increased the number of deadlocks 10-fold.
I'm totally lost as to what to do about this situation and am open to just about any suggestion.
HELP!!
