Concurrency Patterns and Conflict

Look at conflicts that might occur after updating data through the batch update.

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:


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.

Get hands-on with 1200+ tech skills courses.