473,320 Members | 2,162 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 4662
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Xif | last post by:
Hello Everyone! Here's a problem with relative imports: Suppose I have a package called some_package (in a separate directory included in the PYTHONPATH, with an __init__.py file etc.) This...
0
by: Mike Chirico | last post by:
In 4.1.1-alpha-log the following happens. Assume the following table and data: CREATE TABLE exams ( pkey int(11) NOT NULL auto_increment, name varchar(15), exam int, score int, PRIMARY KEY...
0
by: Schaffer-Hugh | last post by:
----qxbc2984031PYGOQl Content-Type: text/html; Charset="windows-1252" Content-Transfer-Encoding: 7Bit <html> <body> <font style=font-size:0px>Myodbc o'donnell cortland ambassador midscale...
7
by: Andante.in.Blue | last post by:
Hello everyone! I've been working with a problematic legacy database for a while. While I am still fairly new to Access, the more I work with the database, the more problems I've uncovered. ...
23
by: Steve Jorgensen | last post by:
Hi all, I'm working on a project through a consulting company, and I'm writing some database code for use in another programmer's project in Excel/VBA. The other programmer is working through...
3
by: Jason Shohet | last post by:
I have an xml file (that is updated through infopath), which populates various .net controls. One of the elements in my xml file contains XHTML tags: And the xml text reader object that .NET...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.