Table locking doesn't prevent duplicate INSERT

[cmayo]

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?