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.
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
