PostgreSQL Locking – Prevent Locking When Parent Table is Updated

database-designlockingpostgresql

I have a simple use case wherein I update the parent table when a row is inserted/deleted in the child table.

Suppose we have a Products table and Items table and ProductId is the foreign key in the Items table.

Whenever an item is inserted or deleted in the Items table, an update is made in the Products table corresponding to the item. An update is simply a query like:

UPDATE "Products" SET "PropertyX" = $1, "UpdatedAt" = $2 WHERE "Id" = $3

Problem:

If the items inserted/deleted in the items table correspond to the same product in the products table then the database load increases considerably causing timeouts even for 3K reqs/min due to Lock:transactionid waits.

I tested this on Aurora Postgres (with 4 CPUs, 16G Ram) and this happens even if the number of rows in the Items table is in the thousands and the number of rows in the Products table is in the hundreds. I used .net core with entity framework.

Best Answer

My advice is to use a “deferred constraint trigger” to update the "Products" table whenever "Items" is modified.

Such triggers are AFTER triggers that are not run immediately after the statement, but at the very end of the transaction.

The advantage here is that the rows in "Products" won't be locked any longer than necessary, and your concurrency will be much better.

Related Question