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?

Answer 1

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.