Hi all,
I\'m working through an issue that I\'m sure lots of people have already figured out, locking a table during a series of INSERTS to prevent duplicate entries:
OS Solaris 8
PHP 4.2.3
MySQL 3.23.53
Table type: MyISAM
The scenario is this:
1) A user submits a form containing about a hundred form fields.
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, 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
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 because the first had locked the tables, resulting in:
INSERTED DATA, THREAD1
INSERTED DATA, THREAD1
INSERTED DATA, THREAD1
INSERTED DATA, THREAD2
INSERTED DATA, THREAD2
INSERTED DATA, THREAD2
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. Can someone tell me the correct way to do this?