Jump to content


Photo

Table locking doesn't prevent duplicate INSERT


  • Please log in to reply
No replies to this topic

#1 cmayo

cmayo
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 10 October 2003 - 02:46 PM

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.


<?// PseudocodeLOCK TABLESforeach($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, THREAD1INSERTED DATA, THREAD1INSERTED DATA, THREAD1INSERTED DATA, THREAD2INSERTED DATA, THREAD2INSERTED DATA, THREAD2

Instead, the inserts from the two threads were still interleaved:

INSERTED DATA, THREAD1INSERTED DATA, THREAD2INSERTED DATA, THREAD1INSERTED DATA, THREAD2INSERTED DATA, THREAD1INSERTED 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?




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users