Pre-requisites

Postgresql

Spring boot with spring data jpa

Problem

I have 2 tables. Products and ProductsLocationCounter. Each product has a location_id and counter_value fields among others. location_id is also the primary key of ProductsLocationCounter. The ProductsLocationCounter is meant to keep a counter of the number of products grouped by a specific location_id whenever a new product is added. The problem is that I also need the counter value at that point in time to be attached to the product entity.

So the flow would be like

 1. create product
 2. counter_value = get counter
 3. increment counter
 4. product.counter_value = counter_value

Of course this has to be done in a concurrent matter. Now, i've read/tried different solutions.

1. this stackoverflow post suggests that i should let the db to handle the concurrency, which sounds fine by me. But the trick is that I need the value of the counter in the same transaction. So I've created a trigger

CREATE FUNCTION maintain_location_product_count_fun() RETURNS TRIGGER AS
$$
DECLARE
counter_var BIGINT;
BEGIN
  IF TG_OP IN ('INSERT') THEN
    select product_location_count.counter into counter_var from product_location_count WHERE id = new.location_id FOR UPDATE;
    UPDATE product_location_count SET counter = counter   1 WHERE id = new.location_id;
    UPDATE products SET counter_value = counter_var WHERE location_id = new.location_id;
  END IF;
  RETURN NULL;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER maintain_location_product_count_trig
AFTER INSERT ON products
FOR EACH ROW
EXECUTE PROCEDURE maintain_location_product_count_fun();

and tested it with a parallel stream

    IntStream.range(1, 5000)
        .parallel()
        .forEach(value -> {
          executeInsideTransactionTemplate(status -> {
            var location = locationRepository.findById(location.getId()).get();
            return addProductWithLocation(location)
          });
        });

Got no duplication on the counter_value column. Is this trigger safe for multi-threaded apps? Haven't worked with triggers/postgresql functions before. Not sure what to expect

1. The second solution I tried was to add PESIMISTIC_WRITE on findById method of the ProductsLocationCounter entity but i ended up getting cannot execute SELECT FOR UPDATE in a read-only transaction even though i was executing the code in a @Transactional annotated method ( which by default has read-only false).

2. The third one was to update and retrieve the value of the counter in the same statement but spring jpa doesn't allow that (nor the underlying db) as the update statement only return the number of rows affected

Is there any other solution or do i need to add something to the trigger function to make it threadsafe? Thank you

CodePudding user response：

This is how I've achieved what i needed.

Long story short, i've used a sql function and I called it inside repository. I didn't need the trigger anymore.

https://stackoverflow.com/a/74208072/3018285