Race conditions with mysql_insert_id()
Posted: Wed Feb 27, 2008 12:04 pm
Hiya,
I was hoping I would never have to think about this problem but alas I *think* I do.
Followed by:
(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
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
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 (...)Code: Select all
PHP (step 2a): $foo_id = mysql_insert_id()
PHP (step 2b): $y = x($foo_id)Code: Select all
MYSQL (step 3): INSERT INTO bar (foo_id, y) values ($foo_id, $y)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