OT: Berkeley databases (was Re: [Mailman-Developers] (More) pristine archives)

[Richard Barrett]

At 13:59 29/08/2002 -0400, Barry A. Warsaw wrote:

>Are there any BerkeleyDB experts on this list?  Apologies for the
>off-topic message, but if anybody has any clues I'd appreciate an
>*off-list* response.
>
> >>>>> "SP" == Simone Piunno <pioppo@ferrara.linux.it> writes:
>
>     SP> It does on BerkeleyDB too.
>
>     SP> Also:
>     |  MyISAM has table-level locking
>     |  BDB    has page-level  locking
>     |  InnoDB has row-level   locking
>
>I've been doing a lot of work with straight BerkeleyDBs for Zope's
>ZODB, and I've run into a possibly fatal problem, for our application.
>
>The basic problem is that a Zope transaction is essentially unbounded
>in the number of objects it touches.  Each object modified translates
>into updates to one or more BDB tables.  I'm using BDB BTrees and
>transactions, so that translates to one lock per level of the database
>plus one lock per page.  It's /possible/ that transactions can touch a
>huge number of pages, and because BDB allocates a static number of
>locks (growable, but only before the environment is opened), it's
>likely that we'll hit a transaction that exhausts the locks.
>
>There seems to be no way to avoid this.  Cranking the BDB locks up
>just begs the question; eventually we run out of locks anyway, plus
>the more locks you allocate the more resources you consume.  We could
>'solve' this if BDB supported table-level locking, because we'd just
>lock the one or two tables that have unbounded updates and be done
>with it.
>
>Any suggestions folks have would be greatly appreciated.  If more
>information is needed, contact me directly.
>
>ObMailman: It would be cool if MM3.0 had a ZODB backend, and I'd love
>to use a Berkeley based storage for that backend.  No, this won't be
>/required/ for MM3.0 so you can stop fretting. ;)
>
>Thanks,
>-Barry

Although I'm no expert, my impression is that the Multi-Version Concurrency 
Control approach of PostgreSQL is a better fit with the transaction model 
of ZODB, certainly as it is used in Zope for instance, than record and 
table locking schemes used by such other databases that even encompass the 
concept of a transaction. It is also my impression that MVCC gives a better 
fit for doing transaction rollback and retry to resolve database update 
contention.

That said, any application doing bulk updates of a database that doesn't 
split the activity into coherent, commit-able sub-transactions is looking 
for trouble.

I would venture that if you have to do such an update then it would be best 
done by locking the whole database and ripping through the job and then 
letting everybody else back in. Rolling back and retrying a transaction 
involving very large numbers of database changes is not good stuff and if 
you get enough contention the big transaction will never succeed.