Concurrency Patterns and Conflict

Prevention of concurrency patterns conflicts

There are two things that you can do to avoid the problem.

Manual lock

The first thing is to make it so you’re doing only one batch update at any time by building your application around that constraint. A good way to implement that idea is with a manual LOCK command, as explained in the explicit locking documentation part of PostgreSQL:

LOCK TABLE target IN SHARE ROW EXCLUSIVE MODE;

That lock level is not automatically acquired by any PostgreSQL command, so the only way it helps us is when we’re doing that for every transaction we want to serialize. When we know we’re not at risk (that is, when not playing the “insert or update” dance), we can omit that LOCK.

The on conflict clause

Another solution is using the new in PostgreSQL 9.5 on conflict clause for the insert statement.