1

I'm fairly new to database administration and PostgreSQL, and there's some things I'm not familiar with, even tho I've read a lot of the PostgreSQL documentation.

Here is a simplified version of the tables in my problem :

USERS_PRODUCTS table

user_id (PK, FK) product_id (PK, FK) ownership
1 1 owned
1 2 owned
1 3 wished
2 1 NULL
2 2 NULL
2 3 owned

PRODUCTS table

id (PK) owned_count wished_count
1 1 0
2 1 0
3 1 1

On each UPDATE of USER_PRODUCT row, a function is triggered, sending an UPDATE on the corresponding PRODUCT row (+/- 1 in owned_count or wished_count column).

I was expecting the triggered functions to introduce some issues regarding concurrency when multiple users set ownership on the same product id (lost updates), but after some load testing, it appears that there's no issue, without any explicit locks.

So my problem is that I don't understand what concurrency mechanisms are at work in order to avoid these lost updates. It looks like magic to me and I'm not ok to push that in production without understanding it.

I didn't find anything in the documentation regarding triggered updates and lock mechanisms, all help is welcome (link to doc, facts, thoughts).

EDIT : Here is a dbfiddle with all the necessary data

Improve this question
6
  • Not an answer (because it would just be retelling the entire concurrency control section from the docs), but, generally speaking, you shouldn't store something that can be computed at runtime.
    – mustaccio
    Commented May 27, 2022 at 13:23
  • Let's say I have 100 000 users with at least 5 products owned, I would perform 500 000 rows read in order to have the counts for a product, and do that for every product I select ? That seems unoptimised. Also I've read concurrency control section and nothing is specified regarding updates inside triggered functions, using the default transaction isolation there should be aborted updates, or lost updates, but there's none when I test with heavy loads.
    – Damien
    Commented May 27, 2022 at 13:35
  • Is "unoptimised" your opinion, or did you measure the performance and, after tuning, still found it unacceptable? By the way, there's nothing special about "updates inside triggered functions" -- they are just regular updates.
    – mustaccio
    Commented May 27, 2022 at 13:39
  • I did test for a 100 products : doing a select with already computed counts has a cost of 0.00..5.01, and doing a select with join on the sum of counts has a cost of 286.48..291.76. Given that accessing products is the main feature of the app, I think doing it on each select is not optimised. Plus I must be able to select and order by these counts. For the update part I had a feeling that there's nothing special, in that case, shouldn't default transaction isolation (read committed) have some concurrency issues without explicit locking ? I'm thinking about lost updates
    – Damien
    Commented May 27, 2022 at 14:26
  • Looking at the query cost is meaningless. Since you're not showing any code, it's impossible to say anything about the possibility of lost updates.
    – mustaccio
    Commented May 27, 2022 at 14:44

1 Answer 1

Reset to default
3

The magic is called "row lock". The updates on the counter column are serialized by row locks, so there cannot be any race condition. A lost update can never happen if you read and write the data in a single UPDATE statement.

Improve this answer
3
  • Thank you fot he answer, indeed after looking at pg_locks there's some RowExclusiveLock. So from what I understand that kind of lock is a table lock, but it locks only a single row based on an id just like we do when using an advisory lock ?
    – Damien
    Commented May 28, 2022 at 13:10
  • There must have been an additional row lock, but row locks are not stored in the lock table for very long. You could use the pgrowlocks extension to find out more.
    – Laurenz Albe
    Commented May 28, 2022 at 15:28
  • When I inspect that table it’s not always there, but during a load testing (thousands of requests per second) I can see some locks every few tries. Thanks for the help I’ll take a look at that extension.
    – Damien
    Commented May 30, 2022 at 7:22

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.