Update and insert in postgres with parallel queries

Question

Will the following case work correctly? I open a transaction on the backend, do an update, check how many rows have been changed, and if no rows have been changed, I do an insert, then commit the transaction. I can't use unique index and "insert ... on conflict do update" due to business logic. There can be several requests with the same parameters at the same time. Wouldn't there be multiple inserts in this case?

transaction isolation level - read committed

I expect only one insert will always be done and all updates will be done correctly.

Answer 1

It should function properly. Each statement within the transaction will only see committed data up to the beginning of the statement if the isolation level is set to read committed.

To avoid multiple updates or duplicate insertion, concurrent requests with the identical parameters will each individually execute the update and check for affected rows. As each request runs within an isolated view of the data, it is ensured that just one insert is carried out even if no rows were altered during that particular request. To accomplish the desired behaviour, it is crucial to maintain the read committed isolation level and the transaction boundary throughout the operation.