[Dbix-class] is it safe use find_or_create in "read committed" transaction level

Peter Rabbitson rabbit+dbic at rabbit.us
Mon Sep 20 09:43:14 GMT 2010

Wei Gui wrote:
> I start use DBIx in my project. and I read this from
> DBIx::Class::ResultSet document
>>> 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.
> IMO, put find_or_create inside a transaction only works if the
> transaction level is serializable. But for Oracle whose default
> transaction level is "read committed", which means race condition in
> find_or_create may still happen even in a transaction.
> Can someone confirm it. if it is true, how use DBIx handle it
> thanks,

find_or_create is just a convenience method, so you don't have to
write one yourself. It is not (and can not be made) race-proof.

Consider the possibilities:

1) We use a transaction the way we do now - depending on the isolation
you use the 2nd racing select will either block, or will continue
and then fail on insert. We can not mandate a certain isolation level
so eventually it may fail (but it will fail immediatelly)

2) We issue the select with a read-lock. This however means that the
lock will persist until the end of the current transaction, which
may be coming *thousands* of statements later, which will effectively
lock most of your database. So instead of an exception *now*, the user
sees a locked database for... a while.

3) We try to create the row first, going in blind. This is a good idea
in theory, however it has a nasty side effect that it makes the current
transaction no-longer-commitable. So using transactions in a race-prone
environment is out as well (the user sees random transactions failing
for no apparent reason)

Thus DBIC implements 1) which is the most predictable way to guard
against races.

More information about the DBIx-Class mailing list