Concurrency Patterns and Conflict
Look at conflicts that might occur after updating data through the batch update.
We'll cover the following
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.
Get hands-on with 1200+ tech skills courses.