How to do get_or_insert_by in Ecto without race condition?

This is not correct. Isolation level will not ever cause a table to be locked by an insert nor other DML command. Locks and isolation level are separate concepts. According to the Postgres documentation, these are the only commands that lock a full table: ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, and VACUUM.

DML commands like insert/update obtain ROW EXCLUSIVE locks.

Isolation level is concerned with isolation between transactions, and preventing dirty reads/phantom reads. SERIALIZABLE simply means the transactions must have the same semantics when running in parallel, that they would have running one after the other. Yes this requires read locks, and not reading new rows from another transaction but that does not lock the whole table against inserts.

8 Likes