PostgreSQL triggered update function lock mechanism

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply