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

[Bill Moseley]

On Mon, Sep 20, 2010 at 2:43 AM, Peter Rabbitson
<rabbit+dbic at rabbit.us<rabbit%2Bdbic at rabbit.us>
> wrote:

>
> 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.
>

And the DBA sends a nasty email about the long transaction.



>
> 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)
>

What if DBIC did the create_or_select type of call if not currently in a
transaction, otherwise fall back to #1?  Would that be generic enough to
help with the issue?

Frankly, I'm a bit shocked by how often I'm seeing the race condition hit.
 Obviously, find_or_create is a useful and needed convenience, so making it
a bit less likely to fail would also be convenient.


-- =

Bill Moseley
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20100924/1b0=
4dbd7/attachment.htm