PostgreSQL triggered update function lock mechanism



  • 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 https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e3b5632d95381963353d68be6b50096b with all the necessary data



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2