I'm sorry if it's a very basic question, but would really appreciate educated advice on this:
Say I have an operation, which first has to read from a table and then insert a row if the read value satisfies a certain condition, and throw if it doesn't, e.g. I have a following table:
id | value --------------- serial | integer
And before inserting a row I need to first:
select sum(value) from table;
value of the row, which I wanna insert is less than 100 - insert, if no - throw.
I need to somehow make those reads be processed only one-by-one, because if I have 2 concurrent queries, both would read, get that the sum is less than 100 and both insert, which would result in an undesired state, which would have been avoided if queries were executed sequentially.
What would be optimal solution to ensure one-by-one processing here?
So far I've tried doing
select for update to lock concurrent read operations, but I have a feeling it might be not optimal here...
I'm also aware I could do that with a before insert trigger but the nature of this logic seems not to fit there because the threshold (
100 in the example) comes from application layer runtime and is actually different every time.
I've read a bit about transaction modes (e.g. serializable) but not sure if that's what I need here...