Re: database locking for item increment/decrement?

[mikeh@minivend.com]

******    message to minivend-users from mikeh@minivend.com     ******

Quoting Larry Leszczynski (larryl@furph.com):
> 
> Hi All -
> 
> > [data
> >       table=inventory
> >       column=qty
> >       row="[item-code]" 
> >       value="-[item-quantity]"
> >       increment=1
> > ]
> 
> Is there any locking on the database when this kind of increment/decrement
> is done?  Hope so, maybe Mike can verify...
> 
> Someone had posted a message that they keep item quantities in the
> products file, rather than a separate inventory file, for sake of
> simplicity.  Assuming the database is locked while increment/decrement
> happens, wouldn't that affect the performance seen by other users trying
> to browse the products file at the same time?  Or is it possible to
> read-only while another process has a write lock? 
> 

It is indeed locked if the DB supports locking. For GDBM, that is the
case; for DB_File it is if you use 2.0.x of the Berkeley DB. For SQL,
it probably is not, as there is no way I know of to do a read-modify-write
portably across architectures. If there is, I would gladly incorporate
it. A good implementation of a DBD driver should be OK, because I don't
undef the statement handle doing the select until after the modify, and
it should not allow another connection ID to modify that record until
the statement handle goes out of scope. But that is something I have
no control over, it depends on the driver.

As far as the performance aspect, it *is* better to use a separate
inventory table, as indeed an exclusive lock (which is needed unless you
are highly tuning the database driver) prevents read access to to other
processes. Bear in mind, though, that a session is not held off until
it tries to access that database, as MiniVend uses as "dummy" database
structure to allow a large number of DB tables to be attached while not
dragging down performance. If I didn't do that, any catalog that had 20
tables (which quite a few do) would be untenable as it opened every one
in turn. Unless you are taking an awful lot of orders performance should
not be greatly affected. Still in all, a separate table should be used.

-- 
Mike Heins                          http://www.minivend.com/  ___ 
                                    Internet Robotics        |_ _|____
Fast, reliable, cheap.              131 Willow Lane, Floor 2  | ||  _ \
Pick two and we'll talk.            Oxford, OH  45056         | || |_) |
 -- unknown                         <mikeh@minivend.com>     |___|  _ <
                                    513.523.7621 FAX 7501        |_| \_\
-
To unsubscribe from the list, DO NOT REPLY to this message.  Instead, send
email with 'UNSUBSCRIBE minivend-users' in the body to Majordomo@minivend.com.
Archive of past messages: http://www.minivend.com/minivend/minivend-list