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!!