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

Damien
  • 113
  • 4
  • 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 May 27 '22 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 May 27 '22 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 May 27 '22 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 May 27 '22 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 May 27 '22 at 14:44
  • I've added a dbfiddle to my question, all the necessary db code is there – Damien May 27 '22 at 15:15

1 Answers1

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.

Laurenz Albe
  • 51,298
  • 4
  • 39
  • 69
  • 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 May 28 '22 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 May 28 '22 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 May 30 '22 at 07:22