Does updating a column with foregin key contraint can lock the referenced table?

Question

I have one table, which is heavily updated in my system by process A. This is the simplified table:

db=# \d employee;
                                       Table "public.employee"
     Column      |            Type             | Collation | Nullable |                   Default
-----------------+-----------------------------+-----------+----------+---------------------------------------------
 id              | integer                     |           | not null | nextval('employee_id_seq'::regclass)
 name            | character varying           |           |          |

Indexes:
"employee_pkey" PRIMARY KEY, btree (id)

And I have a table which is referencing that table:

db=# \d employee_property;
                                       Table "public.employee_property"
     Column      |            Type             | Collation | Nullable |                   Default
-----------------+-----------------------------+-----------+----------+---------------------------------------------
 id              | integer                     |           | not null | nextval('employee_property_id_seq'::regclass)
 type            | character varying           |           |          |
 value           | character varying           |           |          |
 employee_id     | integer                     |           | not null |

Indexes:
    "employee_property_pkey" PRIMARY KEY, btree (id)
    "employee_property_employee_id_type_value_key" UNIQUE CONSTRAINT, btree (employee_id, type, value)
    "ix_employee_property_employee_id" btree (employee_id)
Foreign-key constraints:
   "employee_property_employee_id_fkey" FOREIGN KEY (employee_id) REFERENCES employee(employee_id) ON DELETE CASCADE DEFERRABLE

I am trying to understand if I am updating the employee_property table heavily by process B in the system, might it cause some locks or any other side effects which might affect the process A which updates the employee table?

score 1 · Accepted Answer · answered Dec 18 '18 at 16:14

If you insert a row in employee_property or update the employee_id column of an existing row, a FOR KEY SHARE lock is placed on the row the new employee_id refers to.

This lock will block any concurrent attempt to delete the referenced employee row or update any PRIMARY KEY or UNIQUE columns. Updates to the locked employee row that do not modify a key column will work, because they only require a FOR NO KEY UPDATE lock on the row, which is compatible with FOR KEY SHARE.

The reason for this is that PostgreSQL must ensure that the referenced row cannot vanish while the transaction that modifies employee_property is still in progress. Simply checking for referencing rows in employee won't be enough, because the effects of a transaction that is still in progress are not visible outside the transaction itself.

Does updating a column with foregin key contraint can lock the referenced table?

1 Answers1