Re: SQL insert () values (),(),(); how to get auto_increments properly?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sat, Feb 13, 2010 at 7:41 PM, Jochem Maas <jochem@xxxxxxxxxxxxx> wrote:

> Op 2/13/10 11:36 AM, Eric Lee schreef:
> >
> >
> > On Sat, Feb 13, 2010 at 6:55 PM, Jochem Maas <jochem@xxxxxxxxxxxxx
> > <mailto:jochem@xxxxxxxxxxxxx>> wrote:
> >
> >     Op 2/13/10 10:08 AM, Lester Caine schreef:
> >     > Rene Veerman wrote:
> >     >> Hi.
> >     >>
> >     >> I'm looking for the most efficient way to insert several records
> and
> >     >> retrieve the auto_increment values for the inserted rows, while
> >     >> avoiding crippling concurrency problems caused by multiple php
> >     threads
> >     >> doing this on the same table at potentially the same time.
> >     >
> >     >> Any clues are greatly appreciated..
> >     >> I'm looking for the most sql server independent way to do this.
> >     >
> >     > Rene
> >     > The 'correct' way of doing this is to use a 'sequence' which is
> >     > something introduced in newer versions of the SQL standard.
> >     > Firebird(Interbase) has had 'generators' since the early days (20+
> >     > years) and these provide a unique number which can then be
> >     inserted into
> >     > the table.
> >     >
> >     > ADOdb emulates sequences in MySQL by creating a separate table for
> the
> >     > insert value, so you can get the next value and work with it,
> without
> >     > any worries. The only 'problem' is in situations were an insert is
> >     > rolled back, a number is lost, but that is ACTUALLY the correct
> >     result,
> >     > since there is no way of knowing that a previous insert WILL
> >     commit when
> >     > several people are adding records in parallel.
> >
> >     this is all true and correct ...
> >
> >     but that doesn't answer the problem. how do you get the IDs of all
> >     the records
> >     that we're actually inserted in a multi-insert statement, even if
> >     you generate the
> >     IDs beforehand you have to check them to see if any one of the set
> >     INSERT VALUEs failed.
> >
> >     @Rene:
> >
> >     I don't think there is a really simple way of doing this in a RDBMS
> >     agnostic
> >     way, each RDBMS has it's own implementation - although many are
> >     alike ... and MySQL is
> >     pretty much the odd one out in that respect.
> >
> >     it might require a reevaluation of the problem, to either determine
> >     that inserting
> >     several records at once is not actually important in terms of
> >     performance (this would depend
> >     on how critical the speed is to you and exactly how many records
> >     you're likely to be inserting
> >     in a given run) and whether you can rework the logic to do away with
> >     the requirement to
> >     get at the id's of the newly inserted records ... possibly by
> >     indentifying a unique
> >     indentifier in the data that you already have.
> >
> >     one way to get round the issue might be to use a generated GUID and
> >     have an extra field which
> >     you populate with that value for all records inserted with a single
> >     query, as such it could
> >     function as kind of transaction indentifier which you could use to
> >     retrieve the newly
> >     inserted id's with one extra query:
> >
> >            $sql = "SELECT id FROM foo WHERE insert_id = '{$insertGUID}'";
> >
> >     ... just an idea.
> >
> >     >
> >
> >
> >
> > Hi
> >
> > I would like to learn more correct  way from both of you.
> > May I ask what is a sequences ?
>
> it an RDBMS feature that offers a race-condition free method of
> retrieving a new unique identifier for a record you wish to enter,
> the firebird RDBMS that Lester mentions refers to this as 'generators'.
>
> to learn more I would suggest STW:
>
>        http://lmgtfy.com/?q=sql+sequence
>
>
 Jochem


Thanks,

Regards,
Eric

> >
> >
> > Thanks !
> >
> >
> > Regards,
> > Eric
> >
> >     --
> >     PHP General Mailing List (http://www.php.net/)
> >     To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>
>

[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux