[Dbix-class] Race condition in find_or_create()

[Tobias Kremer]

As many of you probably know, the current find_or_create()  
implementation contains a race-condition.  
Catalyst::Plugin::Session::Store::DBIC is especially prone to die in  
front of the user when using the flash() method - even more so when  
AJAX calls are involved resulting in two or more concurrent requests  
trying to select/insert the flash data into the database via  
find_or_create().

I proposed the following reverse order implementation on the Catalyst  
mailing list which will hopefully not only reduce the probability of a  
race-condition but will also reduce find_or_create() to a single query  
in the best case.

On Tue, Jul 29, 2008 at 12:49:17PM +0200, Tobias Kremer wrote:
> Maybe it'd be better to ditch find_or_create in favor of something  
> like this:
> eval {
>  $row = $self->model->create( { ... } );
> }
> if( $@ && $@ =~ /duplicate/ ) { # the question about detecting this  
> remains
>  $row = $self->model->find( { ... } );
> }
> This would reduce the query to 1 at best and 2 at worst.


mst responded with:

On 30.07.2008, at 12:26, Matt S Trout wrote:
> It's still probably viable.
> What I'd really like is to add some code to DBIC's storage to know  
> what re
> that /duplicate/ is on whatever DB so we can bury this away from the  
> user
> and DBIC can Just Use It for find_or_create on any supported database.
> Maybe you could wander over to the DBIC list and poke at patching that
> instead?

So, the problem is: How to detect if the insert failed due to a unique  
key constraint for all supported databases?

I'd volunteer to help implementing this but as of now I'm not that  
deep into DBIC to know exactly how to pull this off. So I'd really  
appreciate a few ideas and thoughts.

I suppose we could put a method returning a database-dependent regular  
expression that detects unique key constraint errors into the  
DBIx::Class::Storage::DBI::* classes and use that from within  
find_or_create().

What do you think?

--Tobias