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 butt
[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]