Yes, I know about this. What I plan is to remove the auto_increment. I will
do this by hand locking the entire table just right before inserting the
field. Actually, it is running like this. I am just upgrading the PHP.
I will keep the auto_increment in other tables that does not have this kind
of problem.
Thanks a lot for your help.
Andre
On 1/17/05 9:53 PM, "Paul DuBois" <paul@stripped> wrote:
> At 21:27 -0500 1/17/05, Andre Matos wrote:
>> Thanks Eric, but I can let it increment because I cannot have a gave in the
>> numbers. I think I will need to use MAX() in this case.
>
> Using MAX() won't guarantee that you won't have gaps.
>
> What you're describing cannot be achieved in the general case.
> Consider this scenario:
>
> - Transaction T1 begins, generates an AUTO_INCREMENT value n.
> - Transaction T2 begins, generates an AUTO_INCREMENT value n+1.
> - Transaction T2 commits.
> - Transaction T1 rolls back.
>
> You now have a gap at value n, and a used value of n+1.
> MAX() at this point returns n+1, not n, so that won't
> help you reuse n.
>
> With more than two transactions running simultaneously, each
> of which can roll back or commit, the situation becomes more
> complex.
>
> Might be worth reconsidering whether you really require no
> gaps. It's generally better to try to design an application
> not to have that dependency.
>
>>
>> Thanks.
>>
>> Andre
>>
>>
>>
>> On 1/17/05 8:14 PM, "Eric Bergen" <eric.bergen@stripped> wrote:
>>
>>> Just let it increment. Keeping it incremented is MySQL's way ot
>>> insuring that the same id doesn't get used twice for different
>>> records. It's doing everything correctly.
>>>
>>> -Eric
>>>
>>> On Mon, 17 Jan 2005 19:06:45 -0500, Andre Matos
>>> <amatos@stripped> wrote:
>>>> Hi List,
>>>>
>>>> I have a field in one of my tables that uses auto-increment from MySQL
>>>> 4.1.8-nt (Windows XP).
>>>>
>>>> My problem is to get the last insert ID when the insert fails and I use
>>>> rollback. The MySQL is still incrementing the field. How can I
>>>> avoid this if
>>>> it is possible? I am trying to avoid to use the function MAX() to get
> the
>>>> last ID inserted.
>>>>
>>>> Thanks for any help.
>>>>
>>>> Andre
>
--
Andre Matos
amatos@stripped
