Handling race conditions in PostgreSQL

sql multithreading postgresql concurrency locking

You have to care about the transaction isolation level. It should be set to "SERIALIZABLE".

The reason are Phantom Reads - The transaction doesn't lock the whole table, but only the rows which have already been read by the transaction.

So, if another transaction inserts new data, they haven't been locked yet, and the error appears.

Serializable avoids this, by blocking all other transactions, until this one finished.

You can do this via

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

If you want to learn more about this topic, I can highly recommend you this video: http://www.youtube.com/watch?v=zz-Xbqp0g0A

sql multithreading postgresql concurrency locking

Actually, after some messing with ISOLATION LEVEL SERIALIZABLE as proposed by @maja, I've discovered much simpler mechanism:

PERFORM pg_advisory_lock(id);...# do something that others must wait for...PERFORM pg_advisory_unlock(id);

where id is a BIGINT value which I may choose arbitrarily according to my application's logic.

This gave me both the power and the flexibility I was looking for.

CodeHunter