List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 8 2002 2:53pm
Subject:Re: Can AUTO_INCREMENT return from INSERT function into a table ?
View as plain text  
At 21:36 -0700 4/7/02, Son Nguyen wrote:
>--- BD <bdgridly@stripped> wrote:
>>  Son,
>>
>>  At 09:48 PM 4/7/2002, you wrote:
>>  >mysql> desc threads;
>>
>>+-------------+--------------+------+-----+---------+----------------+
>>  >| Field       | Type         | Null | Key | Default | Extra        
>>  |
>>
>>+-------------+--------------+------+-----+---------+----------------+
>>  >| thread_ID   | int(11)      |      | PRI | NULL    | auto_increment
>>  |
>>  >| subject     | varchar(255) | YES  |     | NULL    |              
>>  |
>>  >| author      | varchar(30)  | YES  |     | NULL    |              
>>  |
>>  >| last_by     | varchar(30)  | YES  |     | NULL    |              
>>  |
>>  >| views       | int(11)      |      |     | 0       |              
>>  |
>>  >| date_posted | datetime     | YES  |     | NULL    |              
>>  |
>>
>>+-------------+--------------+------+-----+---------+----------------+
>>  >6 rows in set (0.00 sec)
>>  >
>>  >   $SQL_insert_thread = "INSERT into threads ";
>>  >   $SQL_insert_thread .= "(subject, author, last_by, views, ";
>>  >   $SQL_insert_thread .= "date_posted) " values ";
>>  >   $SQL_insert_thread .= "(\'some_subject\', \'test_username\',
>>  >   $SQL_insert_thread .= "\'test_username\', 1, now())";
>>  >
>>  >   In the table threads above, I do have a field: "thread_ID" with
>>  >auto_increment value. My question is can I obtain the value
>>  thread_ID
>>  >while I do the insert statement just by 1 mySQL statement?  If yes,
>>  >please can somebody give me a sample code?

No.  You have to create the record first in order to generate the
AUTO_INCREMENT value.

>  >
>>  execute the MySQL query:  select last_insert_id();
>
>1st: select last_insert_id() doesn't work

It should.

>
>2nd: It's not the answer I am looking for ... Because of the race
>condition if two users access the that script at the same time... I
>will have when I leave the INSERT statement !!!

There is no race condtion.  LAST_INSERT_ID() is designed to avoid that
problem; it's connection-specific, so it doesn't matter what other
users are doing.  This is explained in the MySQL manual.

>
>Here is what I mean: think about this situation:
>
>USER 1  ===> INSERT
>              STOP <== due to process scheduling
>
>USER 2  ===> INSERT
>              SELECT last_insert_id();
>
>USER 1  ===> SELECT last_insert_id();
>
>It's the race condition, I am talking about.

LAST_INSERT_ID() makes the race condition irrelevant.
Thread
Can AUTO_INCREMENT return from INSERT function into a table ?Son Nguyen8 Apr
  • Re: Can AUTO_INCREMENT return from INSERT function into atable ?BD8 Apr
    • Re: Can AUTO_INCREMENT return from INSERT function into a table ?Son Nguyen8 Apr
      • Re: Can AUTO_INCREMENT return from INSERT function into a table ?Erlend Stromsvik8 Apr
      • Re: Can AUTO_INCREMENT return from INSERT function into a table ?Paul DuBois8 Apr
    • Error in the log filesSoheil Shaghaghi12 Apr
RE: Can AUTO_INCREMENT return from INSERT function into a table ?Noel Clarkson8 Apr