Andre,
I would recommend a table for recovering id's that are lost due to rollback. Before you actually rollback, take the generated ID and push it into this table. Then change the way you acquire id's on insert. You will want to check to see if this table has an ID before you auto_increment the table you are inserting the record into. This should be a little less resource intensive than to put all data into temporary tables.
Clint
From: Joerg Bruehe <[EMAIL PROTECTED]> To: mysql@lists.mysql.com CC: Andre Matos <[EMAIL PROTECTED]>, Paul DuBois <[EMAIL PROTECTED]> Subject: Re: Rollback and INSERT_ID() or LAST_INSERT_ID() Date: Tue, 18 Jan 2005 11:08:40 +0100
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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
