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?

----- Original Message -----
From: "Adam Royle" <[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
> 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 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]

Reply via email to