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


-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
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