"Michael Gale" <[EMAIL PROTECTED]> wrote in message
> 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
> 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

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to