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.
