RE: [PHP-DB] Table locking to prevent duplicate inserts?
John, Thanks much for the excellent tips, I guess I was trying to make it too difficult. We're working on the SQL and indexes to alleviate the slowdowns and I don't expect this to really be an issue in the future, it just concerned me that it was even possible... I thought I'd coded it better than that. The duplicates are an issue because this is a stats-gathering application for area high school sports, and duplicate stat entries resulted in doubled stat values. I guess we should also be looking at tightening up our reporting code using SELECT DISTINCT, perhaps, but really hadn't considered that we'd have any dupes to begin with. Adding a unique constraint isn't a bad idea either, but since it'd have to be unique across several fields I'd have to read up and figure out how to do that. Sorry about that; I hate to keep my employers waiting. BTW, I haven't received my paycheck this month. ;) I certainly didn't mean to imply that anyone on the list had an obligation to answer my post and hope no one took it that way. I was just apologizing for the duplicate post and trying to explain why I was trying again. Anyway, thanks again for the help. I'll be able to fix this app and will incorporate your suggestions into my work in the future. Chuck -Original Message- From: John W. Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, October 20, 2003 11:32 AM To: Mayo, Chuck Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Table locking to prevent duplicate inserts? Mayo, Chuck wrote: Hi all, Hope you'll forgive the duplicate post but the list seems to be hopping today (unlike the friday afternoon I sent the original query)... since everyone seems to be awake today I thought I'd try it again: Sorry about that; I hate to keep my employers waiting. BTW, I haven't received my paycheck this month. ;) 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. Okay, first of all, fix this lengthy select or whatever other slowdowns there are in your scripts. 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 Easy way is to set a session variable when they submit a form flagging the form as submitted at time X. If it's been less than X seconds since the last time the form was submitted, then deny the re-submission. Other method would be to supply a unique id to the form in a hidden element. When the form is submitted, put that variable in the session. If the form is submitted, the value is already in the session, so you know this is a duplicate entry. What problems do the duplicate entries cause? You may be able to handle this at the database level with a UNIQUE restraint on your column. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Table locking to prevent duplicate inserts?
Hi all,
Hope you'll forgive the duplicate post but the list seems to be hopping
today (unlike the friday afternoon I sent the original query)... since
everyone seems to be awake today I thought I'd try it again:
---
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 I got exactly the same
results, it's like the two instances are executing exactly in unison.
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
Re: [PHP-DB] Table locking to prevent duplicate inserts?
Mayo, Chuck wrote: Hi all, Hope you'll forgive the duplicate post but the list seems to be hopping today (unlike the friday afternoon I sent the original query)... since everyone seems to be awake today I thought I'd try it again: Sorry about that; I hate to keep my employers waiting. BTW, I haven't received my paycheck this month. ;) 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. Okay, first of all, fix this lengthy select or whatever other slowdowns there are in your scripts. 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 Easy way is to set a session variable when they submit a form flagging the form as submitted at time X. If it's been less than X seconds since the last time the form was submitted, then deny the re-submission. Other method would be to supply a unique id to the form in a hidden element. When the form is submitted, put that variable in the session. If the form is submitted, the value is already in the session, so you know this is a duplicate entry. What problems do the duplicate entries cause? You may be able to handle this at the database level with a UNIQUE restraint on your column. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Table locking to prevent duplicate inserts?
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
