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]
