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

7
votes

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!

2
postgresqldeadlockdatabase-deadlocks
What is your selection criteria for the inner select? – Germann Arlington
@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. – Oleg Golovanov
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. – Germann Arlington
It's hard to say with no DDL and only simplified queries. – Craig Ringer
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 . – Kuberchaun

2 Answers

14
votes

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.

1
votes

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