Hi,

I would like to insert a data (row) into database (MySQL) and than to
generate file whose name contains the ID of the inserted row (ID is
declared as AUTO_INCREMENT). I wanted to extract ID by
mysql_insert_id() function. However, the problem is that my web site
can visit many users and many of them can initiate the describe
procedure. It can happen that between the first user inserts something
into database and executes mysql_insert_id, the second user also insert
something into database. And in this way the mysql_insert_id initiated
by the first user will return ID of insert-operation initiated by the
second user. If it is possible, how one can avoid this problem?

On 26 Jan 2006 12:55:40 -0800, opt_inf_env@yahoo.com wrote:
[color=blue]
>I would like to insert a data (row) into database (MySQL) and than to
>generate file whose name contains the ID of the inserted row (ID is
>declared as AUTO_INCREMENT). I wanted to extract ID by
>mysql_insert_id() function. However, the problem is that my web site
>can visit many users and many of them can initiate the describe
>procedure. It can happen that between the first user inserts something
>into database and executes mysql_insert_id, the second user also insert
>something into database. And in this way the mysql_insert_id initiated
>by the first user will return ID of insert-operation initiated by the
>second user. If it is possible, how one can avoid this problem?[/color]

mysql_insert_id() is connection specific, and MySQL connections aren't shared
between concurrent PHP requests, so this avoids the condition you describe.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

>I would like to insert a data (row) into database (MySQL) and than to[color=blue]
>generate file whose name contains the ID of the inserted row (ID is
>declared as AUTO_INCREMENT). I wanted to extract ID by
>mysql_insert_id() function.[/color]

This works, and it works *PER CONNECTION*.
It returns the last ID inserted *on this connection*.
[color=blue]
>However, the problem is that my web site
>can visit many users and many of them can initiate the describe
>procedure. It can happen that between the first user inserts something
>into database and executes mysql_insert_id, the second user also insert
>something into database.[/color]

Fine.
[color=blue]
>And in this way the mysql_insert_id initiated
>by the first user will return ID of insert-operation initiated by the
>second user.[/color]

No, that won't happen. mysql_insert_id() works *PER CONNECTION*.
[color=blue]
>If it is possible, how one can avoid this problem?[/color]

It already is avoided.

Gordon L. Burditt

opt_inf_env@yahoo.com wrote:
[color=blue]
> It can happen that between the first user inserts something
> into database and executes mysql_insert_id, the second user also insert
> something into database. And in this way the mysql_insert_id initiated
> by the first user will return ID of insert-operation initiated by the
> second user. If it is possible, how one can avoid this problem?
>[/color]

This is not a problem. The function is related to the connection that
created the last post. So even if there are 1000 users between calls,
you'll get the correct id

Andy Hassall wrote:
[color=blue]
> On 26 Jan 2006 12:55:40 -0800, opt_inf_env@yahoo.com wrote:
>[color=green]
>>I would like to insert a data (row) into database (MySQL) and than to
>>generate file whose name contains the ID of the inserted row (ID is
>>declared as AUTO_INCREMENT). I wanted to extract ID by
>>mysql_insert_id() function. However, the problem is that my web site
>>can visit many users and many of them can initiate the describe
>>procedure. It can happen that between the first user inserts something
>>into database and executes mysql_insert_id, the second user also
>>insert something into database. And in this way the mysql_insert_id
>>initiated by the first user will return ID of insert-operation
>>initiated by the second user. If it is possible, how one can avoid
>>this problem?[/color]
>
> mysql_insert_id() is connection specific, and MySQL connections
> aren't shared
> between concurrent PHP requests, so this avoids the condition you
> describe.[/color]

What about if persistant connections are used? I'm a little unclear of
this myself. Does each PHP thread use a different persistant
connection, or can the share them? I would assume the former.

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com

On Fri, 27 Jan 2006 10:06:58 +1300, Chris Hope <blackhole@electrictoolbox.com>
wrote:
[color=blue][color=green]
>> mysql_insert_id() is connection specific, and MySQL connections
>> aren't shared
>> between concurrent PHP requests, so this avoids the condition you
>> describe.[/color]
>
>What about if persistant connections are used? I'm a little unclear of
>this myself. Does each PHP thread use a different persistant
>connection, or can the share them? I would assume the former.[/color]

In multiprocess webservers, e.g. Apache 1.3, it's simple - there's a
completely separate instance of PHP per Apache worker process, each with its
own persistent connection pool, which will have zero or one connection for a
given database and credentials. So, there's nothing else that can use the
connection, since this instance of PHP can only run one script at a time.

The persistent connection list is stored in the "persistent_list" member of a
struct called "executor_globals". From looking through the PHP source code,
there's a section in Zend/zend_globals_macros.h, where if you have "ZTS"
defined (which means you're building a thread-safe PHP) then there's this:

/* Executor */
#ifdef ZTS
# define EG(v) TSRMG(executor_globals_id, zend_executor_globals *, v)
#else
# define EG(v) (executor_globals.v)
extern ZEND_API zend_executor_globals executor_globals;
#endif

... i.e. it defines EG() as accessing a single global copy of this struct if
not building threaded, but if threaded then it uses the Thread Safe Resource
Manager (TSRM), so presumably if you trace this further through the code then
you end up with a copy of executor_globals per-thread.

Going over to php_mysql.c, it uses this "EG" macro to access persistent_list
in php_mysql_do_connect.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Andy Hassall wrote:
[color=blue]
> On Fri, 27 Jan 2006 10:06:58 +1300, Chris Hope
> <blackhole@electrictoolbox.com> wrote:
>[color=green][color=darkred]
>>> mysql_insert_id() is connection specific, and MySQL connections
>>> aren't shared
>>> between concurrent PHP requests, so this avoids the condition you
>>> describe.[/color]
>>
>>What about if persistant connections are used? I'm a little unclear of
>>this myself. Does each PHP thread use a different persistant
>>connection, or can the share them? I would assume the former.[/color]
>
> In multiprocess webservers, e.g. Apache 1.3, it's simple - there's a
> completely separate instance of PHP per Apache worker process, each
> with its own persistent connection pool, which will have zero or one
> connection for a given database and credentials. So, there's nothing
> else that can use the connection, since this instance of PHP can only
> run one script at a time.[/color]

That's pretty much what I thought. Thanks for the clarification.

[snip]

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com