1

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.

1
  • You can't "prevent" locking; you need to tune your transaction performance.
    – mustaccio
    Nov 24, 2020 at 13:16

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.