Why does this DELETE query freezes the DB?

Question

Something strange that I don't understand. When running a select like this :

select count(*) "factures_quantite_achats_prms" 
WHERE "factures_quantite_achats_prms"."quantite_achats_prm_id" IN (3099747, 3099746, 2979429, 2979430)

I get the correct result: 4 rows.

But when running this (same but with delete):

DELETE FROM "factures_quantite_achats_prms" 
WHERE "factures_quantite_achats_prms"."quantite_achats_prm_id" IN (3099747, 3099746, 2979429, 2979430)

The query freezez.

Here is the explain for the delete:

Delete on factures_quantite_achats_prms  (cost=0.43..23.50 rows=4 width=6)
  ->  Index Scan using factures_quantite_achats_prms__quantite_achats_prm_id__idx on factures_quantite_achats_prms  (cost=0.43..23.50 rows=4 width=6)
        Index Cond: (quantite_achats_prm_id = ANY ('{3099747,3099746,2979429,2979430}'::integer[]))

Also, when looking for locked tables, here is the result:

  pid  | usename | blocked_by |                                                                                              blocked_query                                                                                               
-------+---------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 28097 | Project  | {14713}    | DELETE FROM "factures_quantite_achats_prms" WHERE "factures_quantite_achats_prms"."facture_id" = $1 AND "factures_quantite_achats_prms"."quantite_achats_prm_id" IN (3099747, 3099746, 2979429, 2979430)

So the conclusion is that the DELETE query locks the table but why?

looking at only the table isn't enough, what are the refential constraints, triggers etc. your query seems to have the delete lock on the table, but probably doesn't get the other locks it needs — Turo, Jun 08 '20 at 15:41
Ups, bad eyesight, blocked_by, not locked_by, see https://stackoverflow.com/questions/26489244/how-to-detect-query-which-holds-the-lock-in-postgres to find the reason for the lock — Turo, Jun 08 '20 at 15:58
Your delete statement is blocked by the session 14713 - probably that session (connection) did some (other) DML on that table — , Jun 08 '20 at 16:03

score 0 · Accepted Answer · answered Jun 08 '20 at 15:59

DELETE statement does not freeze a database and does not lock the table in exclusive mode: it mainly locks or tries to lock all selected rows in exclusive mode.

If a DELETE query is waiting on another transaction it is likely that the other transaction has already locked some of the rows.

Why does this DELETE query freezes the DB?

1 Answers1