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
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
