[Catalyst] Duplicate entries with C::P::Session::Store::DBIC
and MySQL - new findings
Peter Corlett
abuse at cabal.org.uk
Tue Aug 26 11:14:21 BST 2008
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.
More information about the Catalyst
mailing list