Re: Questions about multiuser locking in database design

From: Jerry Gitomer <>
Date: Tue, 29 Jan 2002 23:58:43 -0500
Message-ID: <>

Walt wrote:
> Friendly (hopefully) Database mavens:
> I'm in the process (as of this morning, thanks boss :) of designing a flat
> database system that will be used (at least initially) to handle data about
> image files - thumbnail filenames, resolution, layer count, colors used, that
> sort of thing. I'd like it to be pretty general, though, since I have to write
> it anyway. I've got a fairly short time period to do it in, about two weeks.
> The database engine would be embedded in the applications that use the
> databases; the databases would be embodied in files.
> The database structure itself doesn't present much of a problem for me; tables,
> records, fields, and indexing are all pretty familiar to me. My coding skills
> are decent in most areas, so I am quite sure I can kick a flat, single user
> database engine out in a week, maybe less, which would be fine, but...
> There may be more than one application of ours that uses the database. The idea
> of more than one program accessing the same database sort of boggles me.
> It is very possible that more than one application might submit a thumbnail to
> this database at a time, possibly resulting in a collision of some kind and
> subsequent problems. These applications batch process images, so they can be
> running along and be spewing out files at a pretty good clip, especially with
> smaller image dimensions. Eventually... bang! And of course generally, this
> problem can arise in multiuser database use, so I'd like to solve it up front.
> I'd like this to work in at least two environments: Inside MS Windows from our
> applications, and from within a web server (certainly under Linux, maybe under
> other OS's too), from a perl script.
> What I would like to know is if anyone has some general information about this
> issue that I can use to implement some kind of basic, simple, effective and
> reliable anti-collision code. Web page(s), newsgroup post, anything I can get
> to in a hurry.
> I have a vague idea of some kind of semaphore file that says "I'm using the
> database right now so you have to wait." Is that a decent tack to take, or is
> there something better? Do these two operating systems implement named
> general-purpose semaphores or anything cool like that? I used to code on the
> Amiga, and the Amiga OS had some kind of general semaphore system you could
> use, that's pretty appealing.
> walt

Stop and think about your application(s). The only time you have to worry
about collisions is when updating. When an image is being created no one
but the creator can access it until it is actually in the database. As far
as viewing an image is concerned it doesn't matter how many users are viewing
the same image at the same time. As far as other users viewing an image that
is in the process of being changed you have two choices; you either lock the
image from all other users when it is being updated or you only lock from
other attempts to update.

My own preference is to lock only against other attempts to update. This is
because I don't see the difference in the following two situations; 1) user
A accesses an image intending to view it and before she is finished user B
accesses the same image and updates it, and 2) user B does a read for update
on the image and before completing the update user A reads the same image
for viewing.

To my way of thinking you should have three I/O operations:

  1. Read Read the image
  2. Read for update Test and Set a lock flag for the image If the image wasn't locked read it else return an image in use error
  3. Write Write the image Reset the lock flag if it is set

Hope this helps
Jerry Received on Wed Jan 30 2002 - 05:58:43 CET

Original text of this message