[thelist] PHP/mySQL using a checkin/checkout system for multiple editors

Mike Migurski mike-evolt at teczno.com
Thu May 20 16:32:18 CDT 2004

> I¹m in the process of upgrading an older PHP/mySQL admin tool for a
> client,
> I¹m looking for suggestions on the best way for multiple users to be
> able to edit mySQL records and not have any ³edit overlap² // where data
> is open in two places and only one ³change² gets saved.
> Each user of the system can/should have individual account.

Is there a high cost (time/effort) associated with editing the data?
Will an editor be significantly inconvenienced if they go through the
process of modifying the database, only to find that someone else has
beaten them to the punch? Is there a high chance that two editors will
attempt to edit the same piece of information at any given time?

If yes, try a pessimistic lock: When an editor chooses to edit a piece of
data, tag that data as "checked out," and don't let anyone else start the
process of modifying it until the editor has explicitly released the lock.
Worry about ways in which abandoned locks ought to be handled, or pieces
of data that have been "squatted." This is often implemented by marking
the piece of data as read-only, or keeping a separate table of in-progress

If no, try an optimistic lock: When an editor chooses to edit a piece of
data, send along a revision tag. When that editor chooses to commit the
change, compare the editor's revision tag with the revision tag in the
database -- if they are not the same, it means some other editor has made
a change to it and caused the revision tag to change in the meantime, and
the commit should fail. Worry about the frustration resulting from editors
failing to commit time-consuming changes and misunderstandings resulting
from first-come-first-served conflicts. This is often implemented by
adding a "revision" column to every piece of data you use.


michal migurski- contact info and pgp key:
sf/ca            http://mike.teczno.com/contact.html

More information about the thelist mailing list