Race conditions with mysql_insert_id()

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
foobaa
Forum Commoner
Posts: 40
Joined: Tue Feb 13, 2007 10:36 am

Race conditions with mysql_insert_id()

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Race conditions with mysql_insert_id()

Post 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.
(#10850)
foobaa
Forum Commoner
Posts: 40
Joined: Tue Feb 13, 2007 10:36 am

Re: Race conditions with mysql_insert_id()

Post 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?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Race conditions with mysql_insert_id()

Post by Christopher »

Yes.
(#10850)
foobaa
Forum Commoner
Posts: 40
Joined: Tue Feb 13, 2007 10:36 am

Re: Race conditions with mysql_insert_id()

Post 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
Post Reply