On Sat, Feb 13, 2010 at 6:55 PM, Jochem Maas <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 ?
Thanks !
Regards,
Eric
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
