SELECT/UPDATE Deadlock on the Primary Key
<p>I have 2 tables in my MS SQL Server 2008 database:</p>

<pre><code>[Person]
-Id
-Name
-Car_Id
</code></pre>

<p>and:</p>

<pre><code>[Car]
-Id
-Mileage
</code></pre>

<p>Now I have a server process running that constantly updates the Mileage of different cars.</p>

<pre><code>UPDATE Car SET Mileage=1234 WHERE Id=7
</code></pre>

<p>Then I have a client with a grid that shows the persons and the mileage of their cars. This is also periodically updated:</p>

<pre><code>SELECT p.Name, c.Mileage
FROM Person AS p
INNER JOIN Car AS c on p.Car_Id = c.Id
</code></pre>

<p>Quite often now this results in a deadlock on the primary key of my Car table:
<a href="https://i.stack.imgur.com/ASSfH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ASSfH.png" alt="enter image description here"></a></p>

<p>The SELECT query is then aborted by the SQL server. From what I understand this is linked to some sort of range locking on the primary key. One caused by the UPDATE WHERE clause and the other one by the INNER JOIN condition.</p>

<p>Is this assumption correct?</p>

<p>What is the preferred solution to this kind of key lock deadlock problem? I assume the only way around it is to either retry the SELECT, or changing the transaction isolation level. I do not just want to lower the isolation level without understanding completely the problem here.</p>

<p><strong>Edit</strong></p>

<p>More details on the tables:</p>

<pre><code>CREATE TABLE [dbo].[Car](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Mileage] [int] NULL,
 CONSTRAINT [PK_dbo.Car] PRIMARY KEY CLUSTERED 

CREATE TABLE [dbo].[Person](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Car_Id] [int] NULL,
 CONSTRAINT [PK_dbo.Person] PRIMARY KEY CLUSTERED

ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Person_dbo.Car_Car_Id] FOREIGN KEY([Car_Id])
REFERENCES [dbo].[Car] ([Id])

ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [FK_dbo.Person_dbo.Car_Car_Id]

CREATE NONCLUSTERED INDEX [IX_Car_Id] ON [dbo].[Person] 
(
    [Car_Id] ASC
)
</code></pre>

<p><strong>Edit 2</strong></p>

<p>One thing that just occurred to me might be important here as well:
There are always several UPDATE in a transaction on the server side, so not every UPDATE is in a single transaction. Basically all changed cars are updated in one transaction.</p>