SELECT/UPDATE Deadlock on the Primary Key

Question

I have 2 tables in my MS SQL Server 2008 database:

[Person]
-Id
-Name
-Car_Id

and:

[Car]
-Id
-Mileage

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

UPDATE Car SET Mileage=1234 WHERE Id=7

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

SELECT p.Name, c.Mileage
FROM Person AS p
INNER JOIN Car AS c on p.Car_Id = c.Id

Quite often now this results in a deadlock on the primary key of my Car table:

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.

Is this assumption correct?

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.

Edit

More details on the tables:

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
)

Edit 2

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.

What isolation level are you running at? Unless you're running at SERIALIZABLE then the assumption of range locking is not correct (although it's obviously still locking.) Range locks are displayed as RangeS, RangeI or RangeX in your deadlock diagram. — womp, Jan 20 '16 at 21:11
I am running at the default isolation level READ COMMITTED. If it is not a range lock, how can it even happen that I get a deadlock like this on the same primary key. — MuhKuh, Jan 20 '16 at 21:22
So you do not have an index on Person.Car_Id? The foreign key constraint does not serve as an index. — Bruce Dunwiddie, Jan 20 '16 at 21:23
Sorry, forgot to add this one. Of course there is an index, it is a non-unique, non-clustered. — MuhKuh, Jan 20 '16 at 21:35
are you updating thousands of car rows within a single transaction? — Bruce Dunwiddie, Jan 20 '16 at 21:38
The problem is the `SELECT` - it's too "wide" (attempting to read the whole table). The `UPDATE` likely succeeds because the db figures dumping the changes might bad, but doing so to a plain read is likely less harmful. What is it you're doing with the read-only rows? This may partially need to be solved with application design changes. — Clockwork-Muse, Jan 20 '16 at 21:58
What read-only rows? What I still not understand is how this lock can even exist. According to the deadlock graph one process holds an exclusive lock whereas the other one holds a shared lock. But shouldn't it be impossible to have a S and X lock on the primary key at the same time? — MuhKuh, Jan 20 '16 at 22:07

score 1 · Accepted Answer · answered Jan 23 '16 at 22:48

1

It turns out running only one UPDATE in the transaction solves the problem. Thank you all for your inputs.

answered Jan 23 '16 at 22:48

MuhKuh

1,049
10
18

score 0 · Answer 2 · edited May 23 '17 at 12:19

0

To start with, does it help if you change the update so that it doesn't update any rows if the value is already correct?

UPDATE Car SET Mileage=1234 WHERE Id=7 AND Mileage <> 1234;

I don't think I can really do justice in answering your question directly, but try this link, Is it possible to force row level locking in SQL Server? .

UPDATE Car WITH (ROWLOCK) SET Mileage=1234 WHERE Id=7 AND Mileage <> 1234;

edited May 23 '17 at 12:19

Community

1
1

answered Jan 20 '16 at 21:12

Bruce Dunwiddie

2,888
1
15
20

No, because I do not even update when the value did not change. So all updates are real updates. – MuhKuh Jan 20 '16 at 21:23

score 0 · Answer 3 · answered Jan 20 '16 at 21:17

0

Whenever you´re Updating a table you´re obtaining exclusive (write) locks onto the rows of a table up updated/deleted. When a exclusive lock is obtained a share lock (needed for SELECTs) can´t be obtained. The simplest solution to this is to do a rollback of on of the transactions and let the other one complete and try again. I also wouldn´t recommend you a lower isolation level. Serialized should work fine. Do you commit instantly after you Update? A instant Commit; statement after you Update could be a solution to your problem because after a Commit you "give back" your exclusive locks.

answered Jan 20 '16 at 21:17

Moka

61
8

2

False - `READ UNCOMMITTED` allows you to read exclusive-locked rows (among others). If you're at a point you can rollback a transaction, you should be able to commit it too, which is probably the better option. `SERIALIZABLE` is gong to cause problems here because the `SELECT` is too wide - it would lock the entire table, meaning no `UPDATES` will ever be performed. – Clockwork-Muse Jan 20 '16 at 21:47
Readers don’t block Writers, Writers don’t block Readers, Readers don't block readers. Until explicitly using 2phased locks , we should not see dead locks for this particular case . this is the nice article around mvcc. https://vladmihalcea.com/how-does-mvcc-multi-version-concurrency-control-work/ – bhargav Jan 12 '21 at 17:12

SELECT/UPDATE Deadlock on the Primary Key

3 Answers3