List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:January 18 2005 10:08am
Subject:Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
View as plain text  
Hi!

Am Di, den 18.01.2005 schrieb Paul DuBois um 3:53:
> 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.

IMO, Andre's only chance is to code his transactions in such a way that
they need not rollback (only do so if the whole system stops).
One way that comes to my mind is to accumulate all data in some
temporary table, using some other value as ID (or in application
variables), and only after the final "yes, do it" confirmation transfer
them to the "true" tables with the auto-increment ID.

In future releases, stored procedures might be another way to ensure all
actions are grouped without an intervening parallel rollback.

If your concurrency requirements are low and you can stand wait time,
you could keep the "next ID" in an application-controlled table, locked
from its retrieval to a final increment at transaction commit; but I
agree these low requirements are unusual.

As an alternative, a rollback might create a dummy record using that ID
which acts as a placeholder, maybe with a remark "user rollback" or
similar - if that is permissible in the application.

> 
> 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.

Paul, while I agree with that preference, I know that sometimes there is
no choice. As an example, some German bookkeeping regulation requires
you to use dense booking numbers (without gap). So I know of a software
project that used _descending_ numbers because they were faster to
generate in their environment than ascending ones. (This does not solve
the rollback issue, of course.)

Regards,
Joerg

-- 
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

Are you MySQL certified?  www.mysql.com/certification

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