Inserts blocking other tables

Question

We are inserting rows in a PostgreSQL database using 1000 rows transaction/commits on a Pentaho-spoon transformation.

The table has several fields/constraints as foreign keys, i.e. customer_id must exist on customer table and similars.

From time to time we get a deadlock error ...

ERROR: se ha detectado un deadlock
  Detail: El proceso 29996 espera ShareLock en transacción 6643574; bloqueado por proceso 31826.
El proceso 31826 espera ShareLock en transacción 6643573; bloqueado por proceso 29996.
  Hint: Vea el registro del servidor para obtener detalles de las consultas.
  Where: sentencia SQL: «SELECT 1 FROM ONLY "public"."customer_table" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x»

We didn't expect our inserts to block the other tables. The only reason I can imagine is to be sure the customer record is not deleted during the transaction elapsed time.

As we are pretty sure no customer record will be deleted (no way our users can do that), is there any way to avoid the shared lock?

Even if ther is not, I would appreciate any light on what is happening :)

Thks

Answer 1

The INSERT must lock the row in customer_table to make sure that nobody can delete it or otherwise modify the id column until the inserting transaction is finished and the new row becomes visible for everybody.

It could be any UPDATE or DELETE on customer_table that blocks the INSERT.

I recommend upgrading to PostgreSQL 9.3 or later, then the INSERT will not take a SHARE lock on the parent row, but a FOR KEY SHARE lock that will not conflict with UPDATEs on customer_table that do not affect any column with a unique constraint. That will improve concurrency and possibly avoid the deadlocks you experience.