mysql_insert_id() is it not problematic?

[opt_inf_env@yahoo.com]

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?

[Andy Hassall]

Re: mysql_insert_id () is it not problematic?

On 26 Jan 2006 12:55:40 -0800, opt_inf_env@yah oo.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_i d() 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.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Gordon Burditt]

Re: mysql_insert_id () is it not problematic?

>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_i d() 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

[jamen]

Re: mysql_insert_id () is it not problematic?

opt_inf_env@yah oo.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

[Chris Hope]

Re: mysql_insert_id () is it not problematic?

Andy Hassall wrote:
[color=blue]
> On 26 Jan 2006 12:55:40 -0800, opt_inf_env@yah oo.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

[Andy Hassall]

Re: mysql_insert_id () is it not problematic?

On Fri, 27 Jan 2006 10:06:58 +1300, Chris Hope <blackhole@elec trictoolbox.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_lis t" member of a
struct called "executor_globa ls". From looking through the PHP source code,
there's a section in Zend/zend_globals_ma cros.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_g lobals *, v)
#else
# define EG(v) (executor_globa ls.v)
extern ZEND_API zend_executor_g lobals executor_global s;
#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_global s per-thread.

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

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

[Chris Hope]

Re: mysql_insert_id () is it not problematic?

Andy Hassall wrote:
[color=blue]
> On Fri, 27 Jan 2006 10:06:58 +1300, Chris Hope
> <blackhole@elec trictoolbox.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