1

I've been using mysql_pconnect to establish all DB connections on my PHP site, with the theory that it's more efficient (debatable, I know).

I went to use mysql_insert_id to get the ID from a recent INSERT and it occurred to me that given the multi-threaded nature of web requests, I can't guarantee that another PHP script using the same pconnection has made a DB INSERT before my call to mysql_insert_id.

This is kind of a huge deal as I see no other way to guarantee atomicity of the INSERT and ID retrieval, as the ID is not returned by the INSERT query (or I don't know how to get it).

So basically I can never use mysql_pconnect if I want to have thread-safe INSERTS and ID retrieval?

jpeskin
  • 2,801
  • 4
  • 28
  • 27

2 Answers2

1

mysql_insert_id() returns the last id within context of the current connection session. Thus avoiding race-condition problems. There's a bunch of notes/comments regarding this on the php.net manual for mysql_insert_id()

More Info:

This should be easy to test:

<?php
// connection already established
mysql_query("INSERT INTO table VALUES('foo', 'bar')");
sleep(15);
echo mysql_insert_id();

Then see if you can fool it by slamming it with requests during the sleep period.. and check the insert_id results.

Community
  • 1
  • 1
Mike B
  • 31,886
  • 13
  • 87
  • 111
  • I did the suggested test, and I found that a second script that inserted multiple rows into the table during the sleep(15) did not affect the returned value of mysql_insert_id() from the first script. That's promising, but it's possible that mysql_pconnect just decided to create two separate connections instead of sharing them for these scripts. I think I may be coming to the conclusion that Shashwat is correct--PHP will not re-use the same connection as long as any other script is running that is already using that connection. But I can't find hard documentation that confirms this. – jpeskin Sep 30 '11 at 03:40
0

mysql_pconnect means that the connection won't be closed from the php module to the mysql.As there is an overhead of creating a new connection so it can be beneficial if you are creating a lot of connections.

As php executes a request in a single thread.And most probably you will be using 1 connection to the database a call to INSERT and subsequent call to ID retrieval is gonna be ATOMIC.

The MYSQL connection taken by 1 php thread can't be used by another php thread while it is still being in used which ensure atomicity.

shashuec
  • 684
  • 8
  • 20
  • My concern is two separate PHP pages, both of them using mysql_pconnect (and therefore the same connection). I'm skeptical of your assertion that 1 php thread must wait for another thread to close before it can use the same connection. Though I would have to run tests to be sure. – jpeskin Sep 30 '11 at 03:15
  • @jpeskin: Two separate PHP pages can't use same connection.A connection is acquired.1 PHP page can have 2 connections but oppositi is not true.. – shashuec Sep 30 '11 at 03:18
  • it seems you may be right. My tests (see other answer) seem to support your theory. I can't find any documentation on php.net's mysql_pconnect page to confirm this. Do you know where this is documented? Thanks. – jpeskin Sep 30 '11 at 03:43