Re: Questions about multiuser locking in database design
Date: Tue, 29 Jan 2002 23:58:43 -0500
Message-ID: <3C577D83.616E8269_at_erols.com>
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:
Reset the lock flag if it is set
Hope this helps
Jerry
Received on Wed Jan 30 2002 - 05:58:43 CET