Re: [PHP-DB] RE: ensuring unique field value in MySQL using PHP
Dl Neil wrote: Janet, MySQL (and indeed all multi-user databases) has a feature called Locking. What this means is that whilst many users/clients may access a database, apparently simultaneously, when one (or more) is updating the data in some way, everyone else is kept locked-out for the duration. Hopefully the period of time required is so short that other users don't particularly notice. For example, let's say we have a joint bank account. The bank db will maintain a balance figure (say 100). If I'm at one branch of the bank and ask for the balance, it will be given. If at the exact same moment in time, you are at another branch, the SAME number will also be given to you. Now let's get complicated. Having worked out that there is some money, let's say I raid the piggy-bank and ask for 75. The bank computer will say 100 less 75 leaves a balance of 25 and the teller will give me my loot. However if again, at exactly the same point in time) you try to withdraw (a more modest, caring and sharing) 50. If your teller's computer reported a balance of 100, and you got the 50, and the balance was updated to 50, what would happen? I don't know about you, but I don't often get the better of banks... Strangely enough, in database theory this is called the banker's problem. So, when two tellers ask to update an account balance, only one will be given the 'lock' - the other will be momentarily 'locked out'. MySQL is responsible for this timing/choice. (it's one of the management parts of DBMS) The other user/computer is locked out, and in certain situations can figure that out. So what happens next is that your teller does not subtract 50 from 100, but subtracts 50 from the remaining balance, eg UPDATE...SET balance = balance - 50 WHERE a/c nr=... (not SET balance = 100 - 50 ) at which point in time you get embarrassed by the teller, and I get into REALLY hot water! I have really quick reactions: at the first sign of trouble I run away! So yes it is possible that two of your clients will press 'submit' at the same moment in time, but when the processing scripts hit the database, the RDBMS will using a 'lock' to prioritise (even inventing a priority if necessary) one over the other without any intervention from you. You have nothing to worry about (until you let me operate your bank account). Incidentally the 'level' at which a lock is applied varies from DBMS to DBMS. MySQL 'only' has table-level locking. This means no one else can use a table whilst one user is updating. Depending upon transaction rates/response time requirements, the mix of transactions in the system, and the size of the table(s); this might be a problem (eg for our mythical bank). Other DBMS' allow locking right down to the row level. However locking takes time, and so imposes a speed penalty. MySQL is built for speed, doesn't pay a high 'penalty', and in this way gets away with higher level/more widely imposed locking. There is no one 'correct' answer to this conundrum despite the widespread criticism/fear (or even FUD) - everybody's mileage may vary! Don't mean to be picky just to make it a little clearer. Not all database systems lock others from reading a row when updating occurs. Eg. Postgresql, Interbase, Oracle won't stop you from reading a row while it is being updated. Even Sybase and SAPdb can bet set to not to lock readers in certain situations.(Isolation level 0) I understand even Innodb 's MYsql tables allow reading when a row is locked and being updated. MYsql's big lack of features makes it easy to use I suppose. With other databases you would need to carefully choose the correct Isolation Level for the situation. Does that help to clear things up? =dn snip -Original Message- From: Janet Valade [mailto:[EMAIL PROTECTED]] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] RE: ensuring unique field value in MySQL using PHP
Janet,
MySQL (and indeed all multi-user databases) has a feature called Locking. What this
means is that whilst many
users/clients may access a database, apparently simultaneously, when one (or more) is
updating the data in some
way, everyone else is kept locked-out for the duration. Hopefully the period of time
required is so short that
other users don't particularly notice.
For example, let's say we have a joint bank account. The bank db will maintain a
balance figure (say 100). If
I'm at one branch of the bank and ask for the balance, it will be given. If at the
exact same moment in time,
you are at another branch, the SAME number will also be given to you. Now let's get
complicated. Having worked
out that there is some money, let's say I raid the piggy-bank and ask for 75. The bank
computer will say 100
less 75 leaves a balance of 25 and the teller will give me my loot. However if again,
at exactly the same point
in time) you try to withdraw (a more modest, caring and sharing) 50. If your teller's
computer reported a
balance of 100, and you got the 50, and the balance was updated to 50, what would
happen? I don't know about
you, but I don't often get the better of banks... Strangely enough, in database theory
this is called the
banker's problem.
So, when two tellers ask to update an account balance, only one will be given the
'lock' - the other will be
momentarily 'locked out'. MySQL is responsible for this timing/choice. (it's one of
the management parts of
DBMS) The other user/computer is locked out, and in certain situations can figure
that out. So what happens
next is that your teller does not subtract 50 from 100, but subtracts 50 from the
remaining balance, eg
UPDATE...SET balance = balance - 50 WHERE a/c nr=... (not SET balance = 100 - 50 ) at
which point in time you
get embarrassed by the teller, and I get into REALLY hot water!
I have really quick reactions: at the first sign of trouble I run away!
So yes it is possible that two of your clients will press 'submit' at the same moment
in time, but when the
processing scripts hit the database, the RDBMS will using a 'lock' to prioritise (even
inventing a priority if
necessary) one over the other without any intervention from you. You have nothing to
worry about (until you let
me operate your bank account).
Incidentally the 'level' at which a lock is applied varies from DBMS to DBMS. MySQL
'only' has table-level
locking. This means no one else can use a table whilst one user is updating. Depending
upon transaction
rates/response time requirements, the mix of transactions in the system, and the size
of the table(s); this
might be a problem (eg for our mythical bank). Other DBMS' allow locking right down to
the row level. However
locking takes time, and so imposes a speed penalty. MySQL is built for speed, doesn't
pay a high 'penalty', and
in this way gets away with higher level/more widely imposed locking. There is no one
'correct' answer to this
conundrum despite the widespread criticism/fear (or even FUD) - everybody's mileage
may vary!
Does that help to clear things up?
=dn
- Original Message -
From: Adam Royle [EMAIL PROTECTED]
To: Janet Valade [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: 29 January 2002 03:49
Subject: [PHP-DB] RE: ensuring unique field value in MySQL using PHP
Checking a small database for username/password combination would happen so
quick, it would be nearly impossible for two usernames to be entered in.
Your script should work properly, but to make sure no duplicates are
entered, you can change the column definition using the ALTER columnName
command to make sure there are no duplicates. Look in the mySQL
documentation (www.mysql.com) to find the correct command.
Adam
-Original Message-
From: Janet Valade [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 29, 2002 1:24 PM
To: [EMAIL PROTECTED]
Subject: ensuring unique field value in MySQL using PHP
I have a form in which users create their own login name. The code for
storing the login name they created does something like the following:
$userlogin is the name the user typed in the form
$sql = select loginname from login where loginname='$userlogin';
$result=mysql_query($sql);
$num=mysql_num_rows($result);
if ($num 0)
{
echo Sorry, that login name is already taken, Try another;
shows form again;
}
else
insert new record into database;
echo okay, your new account is added;
I am wondering if it is possible that two people could hit the submit button
at the exact same time with the same login name so that the same login name
could get entered twice? Seems unlikely that this would happen by accident,
but is this something a hacker could do on purpose?
Thanks,
Janet
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED
[PHP-DB] RE: ensuring unique field value in MySQL using PHP
Checking a small database for username/password combination would happen so
quick, it would be nearly impossible for two usernames to be entered in.
Your script should work properly, but to make sure no duplicates are
entered, you can change the column definition using the ALTER columnName
command to make sure there are no duplicates. Look in the mySQL
documentation (www.mysql.com) to find the correct command.
Adam
-Original Message-
From: Janet Valade [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 29, 2002 1:24 PM
To: [EMAIL PROTECTED]
Subject: ensuring unique field value in MySQL using PHP
I have a form in which users create their own login name. The code for
storing the login name they created does something like the following:
$userlogin is the name the user typed in the form
$sql = select loginname from login where loginname='$userlogin';
$result=mysql_query($sql);
$num=mysql_num_rows($result);
if ($num 0)
{
echo Sorry, that login name is already taken, Try another;
shows form again;
}
else
insert new record into database;
echo okay, your new account is added;
I am wondering if it is possible that two people could hit the submit button
at the exact same time with the same login name so that the same login name
could get entered twice? Seems unlikely that this would happen by accident,
but is this something a hacker could do on purpose?
Thanks,
Janet
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
