Re: table locking ... not required ?

"Michael Gale" <michael.gale@bluesuperman.com> wrote in message news:Mahogany-0.66.0-5554-20040708-225705.00@michael.bluesuperman.com... > Hello, > > I have a web app that uses a mysql database, now lots of INSETS and > UPDATES are done on a daily bases. Now I do not want to start locking > tables. For the INSERTS it is just a plain insert with a auto incrementing > primary key. So I am sure that multiple inserts would not have a problem is > multiple users tried to insert data at the same time. > > For the UPDATES ... what would have it in theory two people hit the UPDATE > button at the exact same time ? Would one just over write the other ... > cause that is fine ? > > So do you think I need to lock tables ? > > Michael. Hi Michael, there are different approaches to this problem. One is called "Optimictic concurrency control". In Short: Add an int field oca to your table. Increment this value on every update. When someone edits a row, read the value and put it into a hidden field. Then on update check for condition WHERE oca = $ocaFromHiddenField AND id = $id etc. Check the affected rows of your update. If it is 0 someone else has updated between the start and end of your editing (because oca will have been incremented). Then tell your user that someone has just made a change to the record set he is editing and he should abort and start again. A bit hard to explain. Search google for a detailed description. Locking is not a good way here because the table/row may be locked from the start of editing to the end of editing which may take a few minutes and you'd have a problem if the user aborts the editing by clicking on a link or just typing a new address in the address bar. Then there would be no end and your table/row would stay locked... Hope this helps a bit. Regards, Torsten