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]
