Re: 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:jvalade1@san.rr.com]