Postgresql -> deadlock from simple update. I can't get the cause

Asked 12/10, 2012 at 9:35 Answered 6/1, 2016 at 21:13

Solved postgresql deadlock database-deadlocks

Here is table ( simplified ):

                                       Table "public.link"
    Column     |            Type             |                     Modifiers                     
---------------+-----------------------------+---------------------------------------------------
 id            | integer                     | not null default nextval('link_id_seq'::regclass)
 page_id       | integer                     | 
 placed_at     | timestamp without time zone | default now()
Indexes:
    "link_pkey" PRIMARY KEY, btree (id)
    "link_page_id_index" btree (page_id)
Foreign-key constraints:
    "link_page_id_foreign_key" FOREIGN KEY (page_id) REFERENCES page(id) ON UPDATE RESTRICT ON DELETE RESTRICT

And here is query ( simplified ):

UPDATE link SET page_id = ?, placed_at = now() 
WHERE id IN ( SELECT id FROM link ... ) AND page_id IS NOT NULL

Deadlock message:

ERROR: deadlock detected
  Detail: Process 5822 waits for ShareLock on transaction 19705; blocked by process 5821.
Process 5821 waits for ShareLock on transaction 19706; blocked by process 5822.
  Hint: See server log for query details.

How can that query, executed in parallel by several processes, lead to deadlock ?
Thanks!

Pyrexia answered 12/10, 2012 at 9:35 Comment(5)

What is your selection criteria for the inner select? – Allocution 12/10, 2012 at 9:44

@Germann Arlington, It is SELECT from link with JOIN to another table. But does it make sense ? Select can not result in deadlock, the problem with update. – Pyrexia 12/10, 2012 at 10:3

SELECT may be blocked by update depending on transaction isolation level. If your inner select is affected by either of the columns that you are updating then you have a problem. BTW: the dependency may be through JOIN, not just direct WHERE clause... BTW: Why are you updating your page_id at all? It's a foreign key and as such the update may have deeper side-effects too. – Allocution 12/10, 2012 at 10:13

It's hard to say with no DDL and only simplified queries. – Ecclesia 12/10, 2012 at 10:22

What are you omitting part of your query for? Please add the SQL and remove the ... that stuff matters. Also I assume you have multiple sessions running this at the same time? Let's not play guessing games . – Hudgins 12/10, 2012 at 12:1

Session A tries to update ids 10, 2, 30, 4 and session B tries with 40, 30, 20, 10

They both try to lock their respective rows ready for update and A gets 10 and is waiting for 30 while B gets 30 and is waiting for 10. Deadlock.

Your fundamental issue is that you are trying to update (some of) the same ids in concurrent transactions.

Without knowing your database structure and precisely what you're trying to do, it's difficult to suggest the best solution. Typically, you would either make sure different backends don't update the same rows, or reduce timeouts and just retry after a random pause.

Relapse answered 12/10, 2012 at 11:56 Comment(6)

If that turns out to be the case (hard to say given oversimplified question) then a workaround can be to use a SELECT ... ORDER BY ... FOR UPDATE to get the row locks in a deterministic order and only then do an UPDATE of those rows. – Ecclesia 12/10, 2012 at 12:49

@Richard Huxton You are wrong. I am not trying to update links with the same ids, look at WHERE clause: "AND page_id IS NOT NULL". – Pyrexia 12/10, 2012 at 13:3

@Oleg - of course you are. Do you think Session B can look into the future and see that Session A is going to update the same rows as it? Your page_id updates will not be visible to B until A commits (and possibly later than that depending on transaction isolation). – Relapse 12/10, 2012 at 15:4

@Oleg PostgreSQL doesn't deadlock on itself. There's clearly two sessions involved: one with PID 5821, another with PID 5822. Try logging SELECT pg_backend_pid() from your application to find the other (successful) party. – Achelous 12/10, 2012 at 15:48

@RichardHuxton, thanks for great answer! I have never thought that deadlock could happen there :) – Pyrexia 15/10, 2012 at 12:10

Concurrency is always tricky to deal with, even once you know what can happen. – Relapse 15/10, 2012 at 13:3

In most of case deadlock arrives because of circular wait between rows will going to update so if u want to resolve deadlock you can simply use ordering on rows which you want to update

UPDATE link SET page_id = ?, placed_at = now() 
WHERE id IN ( SELECT id FROM link ... order by page_id ) AND page_id IS NOT NULL

Mcnamee answered 6/1, 2016 at 21:13 Comment(0)

Recommended topics

Hot tags