17 messages in com.mysql.lists.win32RE: Auto-Increment
FromSent OnAttachments
Jill...@Aculab.com16 Jun 2003 02:49 
Armin Schöffmann16 Jun 2003 02:59 
Robin Keech16 Jun 2003 03:25 
Jill...@Aculab.com16 Jun 2003 03:28 
Jill...@Aculab.com16 Jun 2003 03:37 
Ignatius Reilly16 Jun 2003 03:38 
Ignatius Reilly16 Jun 2003 04:04 
Armin Schöffmann16 Jun 2003 04:08 
Armin Schöffmann16 Jun 2003 04:08 
Jill...@Aculab.com16 Jun 2003 04:09 
"Juan F. Capristán W."16 Jun 2003 04:14 
"Juan F. Capristán W."16 Jun 2003 04:20 
Jill...@Aculab.com16 Jun 2003 04:25 
Alan McDonald16 Jun 2003 04:27 
Robin Keech16 Jun 2003 05:15 
Ignatius Reilly16 Jun 2003 08:05 
Paul DuBois20 Jun 2003 21:12 
Subject:RE: Auto-Increment
From:Paul DuBois (pa@mysql.com)
Date:06/20/2003 09:12:05 PM
List:com.mysql.lists.win32

At 11:28 +0100 6/16/03, Jill@Aculab.com wrote:

Ok, I thought Armin had solved it for me there, but it seems I was a little hasty. I _DO_ need to do this atomically. If some other thread/process does an insert in between your insert and your "SELECT LAST_INSERT_ID()", the answer will be wrong. I need the answer to be RIGHT.

The answer *will* be right. This is exactly the problem that LAST_INSERT_ID() solves. It returns the most recent AUTO_INCREMENT value generated on the same connection. Activity on other connections is irrelevant. Anyone who claims otherwise simply has not read the manual. :-)

http://www.mysql.com/doc/en/Miscellaneous_functions.html

I'll restate my original question: Given that there may be other people accessing the same table in the same database at the same time (but in a different thread or procress), HOW can you add a record to the table and then find out what value was assigned BY THIS THREAD/PROCESS to the primary key of the newly added record?

1) Insert the record 2) Issue SELECT LAST_INSERT_ID()

As long as you do both in the same connection, LAST_INSERT_ID() returns exactly what you want. No need to lock tables or any other messing around to keep other clients from updating the table. They can update it all they want, and LAST_INSERT_ID() will still return *your* AUTO_INCREMENT value.

Jill

-----Original Message----- From: Robin Keech [mailto:r.ke@synectics.co.uk]

As you have realised, this is a two stage process, not atomic as you asked...

-----Original Message----- From: Armin Schöffmann [mailto:armi@aegaeon.de]

Hi! SELECT LAST_INSERT_ID() regards, Armin.

-----Original Message----- From: Jill@Aculab.com [mailto:Jill@Aculab.com] Sent: Monday, June 16, 2003 11:50 AM To: win@lists.mysql.com Subject: Auto-Increment

Suppose you have a table with an auto-incrementing int as primary key.

HOW can you (atomically) add a record to the table and find out what value has been assigned to the primary key of the newly added record?

Jill

-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com

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