I am using postgres 14.2. I am using jooq and spring-boot in my service.
I have table user where I have a unique constraint on user_id, created_on, I have to update the value if the value of user_id, created_on are already present else insert. I have chosen following query to achieve this
insert into users (?,?,?....) on conflict(user_id,created_on) do update set column1 = ? and ....
Now when two transactions try to do execute above, there might be a situation where both the transactions get no conflict and both try to insert, one transaction fails. How to handle this race condition, where one transaction has to wait if other transaction got go-ahead call to insert. So that both transaction should be successful, order doesn't matter as, when i face this race condition each transaction update different columns.
Asking LLMs gave me answer to use advisory locks.
Is there any elegant way to handle this situation? Be it in my query selection, having better locking mechanism. I am facing these problems very frequently recently.
With INSERT ... ON CONFLICT, the situation you describe can never happen. Even if two of these statements run at the exact same time, only one of them can end up getting no conflict, and the other one will update the row created by the first one.