How to avoid race conditions when using the find_or_create method of DBIx::Class::ResultSet?

Question

From the documentation for find_or_create:

Note: Because find_or_create() reads from the database and then possibly inserts based on the result, this method is subject to a race condition. Another process could create a record in the table after the find has completed and before the create has started. To avoid this problem, use find_or_create() inside a transaction.

Is it enough to just use find_or_create() inside a transaction in PostgreSQL?

Prodigious

Answer 1

No, the documentation is incorrect. Using a transaction alone does not avoid this problem. It only guarantees that the whole transaction is rolled back if an exception should occur - so that no inconsistent state will be persisted to the database.

To avoid this problem you must lock the table - inside a transaction, because all locks are released at the end of a transaction. Something like:

BEGIN;
LOCK TABLE mytbl IN SHARE MODE;

-- do your find_or_create here

COMMIT;

But that's not a magic cure for everything. It can become a performance problem, and there may be deadlocks (concurrent transactions mutually trying to lock resources that the other one has locked already). PostgreSQL will detect such a condition and cancel all but one of the competing transactions. You must be prepared to retry the operation on failure.

The PostgreSQL manual about locks.

If you don't have a lot of concurrency you might also just ignore the problem. The time slot is very tiny so it only very rarely actually happens. If you catch the duplicate key violation error, which will do no harm, then you have covered this, too.

Safe

Other useful pages. Docs: postgresql.org/docs/current/interactive/mvcc.html PostgreSQL version 9.1 or later serializable implementation: wiki.postgresql.org/wiki/SSI Other isolation levels or PostgreSQL versions: postgresql.org/files/developer/concurrency.pdf – Seaborg

But what's the proper way to catch "the duplicate key violation error" in DBIC? – Prodigious

@eugeney: I suppose you open a new question for that, instead of a comment. You can always link to this one to save yourself some typing. – Safe

This helps with the SQL itself, but doesn't help the poster with DBIx::Class, which is what his question was about. – Beriberi

@Wokka: I did provide a solution to the question and it would seem the author of the question was satisfied. I even studied the Perl documentation and pointed out a shortcoming. – Safe

Answer 2

This implementation of find_or_create should prevent the race condition, described in the OP:

eval {
    $row = $self->model->create( { ... } );
}
if($@ && $@ =~ /duplicate/i) {
   $row = $self->model->find( { ... } );
} 

It also reduces find_or_create() to a single query in the best case.

Prodigious

This reverses the logic. But now you have a tiny time slot in which the the entry could be deleted - in which case the logic will fail. Trying to write first is more expensive than trying to read. So this is only an improvement if duplicates are very uncommon. Either way, conflicts should be very rare, because the time slot is tiny. – Safe