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 :
USER_PRODUCTUSERS_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 |
PRODUCTPRODUCTS 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