Repeatable reads in case protection from duplicated inserts in POSTGRES

Question

I would need to perform this kind of logic in a serializable way:

account table has columns: 'accountName', 'location', 'worth', ...
Assume no unique key exists.

Some accounts are logically (according to an application) in a same group.

Example:

accountNameOne - group1 accountNameTwo - group1

accountNewOne - group2 accountNewTwo - group2

There are many clients application trying to insert one account member of their group concurrenlty. I want to have the guarantee to insert only one account per group.

BEGIN;
select *
from account 
where accountName in (list of account names inside a group)
-- if result is not empty exit there's already an account of the group
-- otherwise we want to insert it
INSERT INTO account (accountName, column2, column3, ...)
VALUES ('accountInAGroup', value2, value3, ...);
COMMIT;

As far as I know Repeatable reads isolation level cannot help in this, I am basically left with using SERIALIAZABLE I guess.

Can you help me understand if my reasoning here is correct ? Is Repeatable reads really not enough for this ?

• Laurenz Albe: The question is a bit unclear, but it looks like INSERT ... ON CONFLICT is the answer.
• GionJh: Hey Laurenz, yes sorry, maybe because I mentioned a unique costraint, I don't have an easy way to put in place one, I was looking at pg_advisory_xact_lock, what do you think ?
• Laurenz Albe: An advisory lock cannot be the solution. I suggest that you edit the question and make your SQL sample syntactically correct.

Answer 1

Yes, the SERIALIZABLE isolation level is the only way for the database to guarantee that no anomalies can occur.

Serializing the execution of the function using advisory locks would only help if that function is the only place where data are inserted into this table.

• GionJh: Thanks a lot Laurennz!