468,247 Members | 1,335 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,247 developers. It's quick & easy.

mysql_insert_id() is it not problematic?

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?

Jan 26 '06 #1
6 4472
On 26 Jan 2006 12:55:40 -0800, op*********@yahoo.com wrote:
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?


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 :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Jan 26 '06 #2
>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.
This works, and it works *PER CONNECTION*.
It returns the last ID inserted *on this connection*.
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.
Fine.
And in this way the mysql_insert_id initiated
by the first user will return ID of insert-operation initiated by the
second user.
No, that won't happen. mysql_insert_id() works *PER CONNECTION*.
If it is possible, how one can avoid this problem?


It already is avoided.

Gordon L. Burditt
Jan 26 '06 #3
op*********@yahoo.com wrote:
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?


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
Jan 26 '06 #4
Andy Hassall wrote:
On 26 Jan 2006 12:55:40 -0800, op*********@yahoo.com wrote:
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?


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


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
Jan 26 '06 #5
On Fri, 27 Jan 2006 10:06:58 +1300, Chris Hope <bl*******@electrictoolbox.com>
wrote:
mysql_insert_id() is connection specific, and MySQL connections
aren't shared
between concurrent PHP requests, so this avoids the condition you
describe.


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.


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 :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Jan 26 '06 #6
Andy Hassall wrote:
On Fri, 27 Jan 2006 10:06:58 +1300, Chris Hope
<bl*******@electrictoolbox.com> wrote:
mysql_insert_id() is connection specific, and MySQL connections
aren't shared
between concurrent PHP requests, so this avoids the condition you
describe.


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.


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.


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

[snip]

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com
Jan 26 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Mike Chirico | last post: by
reply views Thread by Schaffer-Hugh | last post: by
7 posts views Thread by Andante.in.Blue | last post: by
3 posts views Thread by Jason Shohet | last post: by
reply views Thread by kermitthefrogpy | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.