Re: 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" <adam@bigbridge.com.au> To: "Janet Valade" <jvalade1@san.rr.com>; <php-db@lists.php.net> 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:jvalade1@san.rr.com] > Sent: Tuesday, January 29, 2002 1:24 PM > To: php-db@lists.php.net > 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: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net > >