[BULK] - Re: [Dbix-class] Issue with DBIx::Class::ResultSet::find_or_create():

[Mesdaq, Ali]

Well I was giving the -1 return value only to illustrate how it could
possibly work. In our case we have a method that is similar to
find_or_create and we call it set_measure and since we KNOW we are using
DBD::mysql we know how to catch the exception sanely in our insert
method catches the exception like this:

$sth->execute($args->{value}) || do
    {
        if ($DBI::errstr =~ m/Duplicate entry.+for key/i)
        {
            # Returns -1 to indicate duplicate entry was found and it
should reselect
            return -1;
        }
        else
        {
            confess DBI::errstr;
        }
    }; 

In our case this works but with the level of abstraction in DBIx::Class
I am not sure what the best method would be to handle various DB's and
how they handle constraints and exceptions. But I think the whole point
of find_or_create method should handle these and no matter what return a
valid id or resultset assuming the data is valid input data.

Thanks,
------------------------------------------
Ali Mesdaq
Security Researcher II
Websense Security Labs
http://www.WebsenseSecurityLabs.com
------------------------------------------

-----Original Message-----
From: Matt S Trout [mailto:dbix-class at trout.me.uk] 
Sent: Saturday, July 28, 2007 1:40 AM
To: dbix-class at lists.rawmode.org
Subject: [BULK] - Re: [Dbix-class] Issue with
DBIx::Class::ResultSet::find_or_create():

On Fri, Jul 27, 2007 at 06:38:19PM -0700, Mesdaq, Ali wrote:
> This came up in the Catalyst list today so I am posting here for 
> discussion or bug reporting.
> 
> The issue is that it seems the find_or_create() call can have a hiccup

> in high load situations where find_or_create is called more than once 
> at the same time with the same value. So what happens is both calls 
> try to do a find and return nothing then they attempt to insert and 
> once succeeds and one gets an error of:
> 
> DBIx::Class::ResultSet::find_or_create(): DBI Exception:
> DBD::mysql::st execute failed: Duplicate entry

My initial response to this was the same as everybody else's, "catch the
damn exception".

My second response was "can't we make this atomic somehow?" ... but of
course the answer is no. We could do transaction stuff and ... throw a
different exception.

I don't like your "return -1" approach though.

Three possiblities:

(1) trap duplicate key exception and do what you describe
    - do DBs give a sanely trappable exception here?

(2) try the re-select no matter what the exception
    - actually -probably- safe. performance issues?

(3) 1, but don't bother with the initial select call, try the insert and
then
    find if it fails
    - would this actually be faster?

Thoughts, people?) try the re-select no matter what the exception
    - actually -probably- safe. performance issues?

(3) 1, but don't bother with the initial select call, try the insert and
then
    find if it fails
    - would this actually be faster?

Thoughts, people?

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class
project?
   Technical Director    Want a managed development or deployment
platform?
 Shadowcat Systems Ltd.  Contact mst (at) shadowcatsystems.co.uk for a
quote
http://chainsawblues.vox.com/
http://www.shadowcatsystems.co.uk/ 

_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive:
http://www.mail-archive.com/dbix-class@lists.rawmode.org/