[Catalyst] Duplicate entries with C::P::Session::Store::DBIC and MySQL - new findings

[Peter Corlett]

On Tue, Aug 26, 2008 at 09:47:59AM +0200, Tobias Kremer wrote:
[...]
> b) Patch DBIx::Class's find_or_create() method and reverse its order
>    (insert first, check for duplicate, then select).
>    Disadvantages:
>      - Not that easy to implement because every database returns
>        a different error to signal a unique key constraint violation.
>      - It's quite possible that this doesn't really solve the problem
>        but just changes the error. If the row is deleted between insert
>        and select (this race condition still exists), find_or_create()
>        will return nothing and AFAICT C::P::Session::Store::DBIC's
>        flash() method expects a working row object.

Also, it'll cancel any transaction in progress. However, that can be fixed
by using SAVEPOINT instead. SAVEPOINT; INSERT ...; ROLLBACK TO SAVEPOINT;
SELECT/UPDATE ...; seems to be the recommended approach for PostgreSQL.
PostgreSQL supports SAVEPOINT as of 8.0; you're SOL with 7.4 and earlier.

(I was in fact considering concocting and submitting a patch as I'm being
bitten by this race condition too.)

I can't speak for whether MySQL's advertised SAVEPOINT support works, or
even does anything other than acknowledge that the keyword exists.

>    Advantages:
>      - find_or_create() will be faster everywhere in many cases.

There would be the overhead of a transaction, but it's a worthwhile cost in
a non-toy application.