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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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 >>> <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
