Dl Neil wrote:
> 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
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
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?
> > -----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]