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