Page 1 of 1

Race conditions with mysql_insert_id()

Posted: Wed Feb 27, 2008 12:04 pm
by foobaa
Hiya,

I was hoping I would never have to think about this problem but alas I *think* I do.

Code: Select all

MYSQL (step 1): INSERT INTO foo (...) values (...)
Followed by:

Code: Select all

PHP (step 2a): $foo_id = mysql_insert_id()
PHP (step 2b): $y = x($foo_id)
(I do processing in step 2b to demonstrate that I may not be able to do the sql in one query - not that I'm doing that at all - should I?) and then

Code: Select all

MYSQL (step 3): INSERT INTO bar (foo_id, y) values ($foo_id, $y)
As most people know, this can cause race conditions if another user does an insert in between the current user's step 1 and 2. I think this may become problematic for me as I'm designing for a high-traffic site with some sensitive data.

The solutions that come to mind are transactions and/or table locking.

This is new territory for me however and my concerns are:
1) Performance - transactions and particularly table locking sound like they could create a severe bottleneck?
2) If I avoid xactions/locking then what are the chances of race conditions messing up?
3) I presume the trick is to selectively use xactions/locking only where critical?
4) Is there something I'm missing? Maybe a trick, or more gotchas, anything...?
5) Is this a problem that by nature is difficult and just needs hardware throwing at it? If so, what solutions??

I've looked at comments at: http://uk.php.net/mysql_insert_id but don't know what to make of it.

Help much appreciated - Thanks

Re: Race conditions with mysql_insert_id()

Posted: Wed Feb 27, 2008 12:08 pm
by Christopher
If your primary key is an autoincrement field then you cannot have a race condition. Each key will be unique and you will get the last id for you connection.

Re: Race conditions with mysql_insert_id()

Posted: Wed Feb 27, 2008 12:13 pm
by foobaa
Thanks for the quick response.

So a single connection will last only for the life of the execution of a script, for a single user?

Re: Race conditions with mysql_insert_id()

Posted: Wed Feb 27, 2008 12:47 pm
by Christopher
Yes.

Re: Race conditions with mysql_insert_id()

Posted: Wed Feb 27, 2008 1:23 pm
by foobaa
Thanks arborint -

Fantastic. I never knew that.

The questions now are:

1) why would anyone want to use transactions or table locking? (While writing answered my own question reading http://www.brainbell.com/tutors/php/php ... bases.html)
Transactions: to ensure a series of steps gets completed as a single unit, or not at all.
Table locking: Answer: transactions won't prevent lost writes and all that stuff I've long forgotten

However:

2) How to decide when to use transactions or table locking without going to unnecessary extremes? What kind of scenarios should a php programmer start considering using DB xactions/ table locking?

(The URL above gives silly examples which it admits are probably more a design problem that needs to handle the delay between users' requests in seconds or minutes - rather than a database and concurrent queries interfering within the timespan of tens of microseconds.)

Thanks again