List:General Discussion« Previous MessageNext Message »
From:Andre Matos Date:January 18 2005 3:17am
Subject:Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
View as plain text  
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


Thread
Rollback and INSERT_ID() or LAST_INSERT_ID()Andre Matos18 Jan
  • Re: Rollback and INSERT_ID() or LAST_INSERT_ID()Eric Bergen18 Jan
    • Re: Rollback and INSERT_ID() or LAST_INSERT_ID()Andre Matos18 Jan
      • Re: Rollback and INSERT_ID() or LAST_INSERT_ID()Paul DuBois18 Jan
        • Re: Rollback and INSERT_ID() or LAST_INSERT_ID()Andre Matos18 Jan
        • Re: Rollback and INSERT_ID() or LAST_INSERT_ID()Joerg Bruehe18 Jan
          • Re: Rollback and INSERT_ID() or LAST_INSERT_ID()Clint Edwards18 Jan