Re: Questions about multiuser locking in database design

Jerry Gitomer wrote:
>
> Walt wrote:
> >
> > 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
>
> 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.

But that doesn't mean that new rows can be added without locking. Here's what can happen without locking (especially in a web context where user A has a slow connection and user B has a speedy one):

  User A opens the database file.

     User B opens the database file.
     User B inserts an image.
     User B saves the file.

Are both images in the file? Nope. So therefore inserting a new image is just as suceptible to collision as an update.

Here's my advice:

• Always use exclusive locks if there is any writing (insert,update,delete).
• Never release the lock before that process is completely done with the file.

> 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.

Since you mention Perl, there are many locking related modules on CPAN, several of which implement semaphore files so they can run with or without native support for flock(). Or better yet, use one of the perl DBI file database modules which provide appropriate locking behind the scenes without you needing to do anything.

-- 
Jeff