Jump to content

Archived

This topic is now archived and is closed to further replies.

cmayo

Table locking doesn't prevent duplicate INSERT

Recommended Posts

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?

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.