Help me please, I'm getting following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 235) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I have this error being thrown every morning when I have most transactions happening with few seconds.

My stored procedure looks like this:

create procedure dbo.sp_storproc

(

@ServerID varchar(32),

@key varchar(32),

@Values varchar(32)

)

as

set nocount on

declare @Error int, @RowCount int

update myTable with (RowLock)

set Values = @Values

where ServerID=@ServerID and Key = @key

-- it has PK for ServerID and Key

select @RowCount = @@ROWCOUNT

select @Error = @@ERROR

if @Error<>0

begin

return -1

end

if @RowCount = 0

begin

insert into myTable with (RowLock) (ServerID, Key, Value)

values (@ServerID, @key, @Values)

select @Error = @@ERROR

if @Error<>0

begin

return -1

end

end

what is happening, I have same application running on 5 servers and trying to run this sp at the same time,

sometimes I can see in about 2500 rows are inserted per second, but sometimes I can see that only 1 record

is inserted and the error log shows me that deadlock exception happened exactly at that same second.

Each server passes it's own @ServerID parameter, so there is no contention for the values accross the servers.

Please advise on the solution of how to avoid deadlock and let all records to be inserted, because usually

out of 9000 records that needs to be inserted with 10 or 5 seconds there is 5 or 6 records that are not

inserted and they're in my exception log.