Hi all,
I'm working through an issue that I'm sure everyone else has already figured
out, how to lock a table during a series of INSERTS to prevent duplicate
entries:
The scenario is this:
OS Solaris 8
PHP 4.2.3
MySQL 3.23.53
Table type: MyISAM
1) A user submits a form containing about a hundred form fields which PHP
needs to insert into a MySQL table.
2) MySQL is busy doing a lengthy SELECT for someone else, so the user's
browser hangs until the SELECT is done.
3) The user gets tired of waiting and hits submit again while his browser is
still hung.
4) When the long SELECT finishes, two PHP threads kick off (I'm assuming),
the user's original submit and his second, resulting in duplicate INSERT's
The first thing I tried was locking the tables at the top of the insert loop
then unlocking at the bottom, i.e.
<?
// Pseudocode
$thread_id = unique id generated with uniqid();
LOCK TABLES
foreach($form_field as $db_field) {
INSERT INTO $table VALUES ('$db_field','$thread_id)
}
UNLOCK TABLES
?>
What I thought would happen is that one PHP thread would have to wait for
the other resulting in:
INSERTED DATA, THREAD1
INSERTED DATA, THREAD1
INSERTED DATA, THREAD1
INSERTED DATA, THREAD2
INSERTED DATA, THREAD2
INSERTED DATA, THREAD2
That wouldn't have solved my dupe issue but would have at least shown me
that locking the tables was the way to go. Instead, the inserts from the two
threads were still interleaved:
INSERTED DATA, THREAD1
INSERTED DATA, THREAD2
INSERTED DATA, THREAD1
INSERTED DATA, THREAD2
INSERTED DATA, THREAD1
INSERTED DATA, THREAD2
Seemed like locking the tables for the duration of the insert loops would do
it but it didn't seem to.
My next avenue of attack was a modification where the script would first
check for an existing record and update it if one was found, inserting a new
record only if there wasn't an existing record, but got exactly the same
results. Anyone have a clue what's going on here, or better still... how the
heck to make these threads play nice?
Thanks
Chuck Mayo
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
