[Zope] Zope in Portals

[Andy Dustman]

On Tue, 28 Sep 1999, Christopher Petrilli wrote:

> > There are a couple possibilities for implementing transactions in
> > ZMySQLDA:
> >
> > 1) Automatic use of LOCK TABLES.
> >
> 
> Excuse me while I lose my lunch. :-)  This is so amazingly hackish as to
> almost be a joke.  This *might* work partially in some half-brain-dead
> fashion in a single-threaded, non concurrent environment, but when you have
> other situations where you might have two people touching tables at the same
> time, in some cases dependent on each other...

In a single-threaded, non-concurrent environment, you wouldn't need LOCK
TABLES at all.

> This is where "SELECT * FROM UPDATE" comes from.

I don't think MySQL supports SELECT ... FROM UPDATE anyway. It certainly
doesn't support sub-selects (but will in the near future in 3.23).
 
> This is just the tip of the concurrency iceberg.  It's all about keeping
> your data sane under load.  I can write all kinds of hacks that will fix it
> in low usage situations, but will they hold up with 100+ concurrent users?

The answer is: It depends on your application. I don't think I'd use MySQL
for a large e-commerce application, but there are certain classes of
problems where it works fine. As an example, I have a mail application
which presently authenticates out of Solid, and I developed a MySQL
interface because we needed more speed out of, and transactions are not
critical, since the database is read-mostly. (Turns out, we may bypass
MySQL for LDAP.)

> Not to slam MySQL, but... it has it's place, but I'm not sure it's under
> heavy update loads.

There were two principles we came up with when working on the Python DB
API 2.0 spec that are relevant. The first, and oldest: The query language
is database-dependent. The second, and relatively new: Don't support
rollback() on databases without transactions. (The old MySQL module
implemented rollback() as pass, which is bad news.) The most the DA should
reasonably be expected to do is raise an exception in this case, which the
caller can (maybe) recover from.

Which leads to scenario #3: Make the developer of the SQL methods write
the necessary SQL statements to implement the desired level of isolation
(if possible), and be prepared to handle an exception and clean up if
rollback occurs. It's not unreasonable to expect someone who's going to
use ZMySQLDA to be familiar with MySQL and know what it's limitations are.

Incidentally, the "+100 users comment" raises the question: Will Zope
allow the same database connection to be used simultaneously in more than
one thread? I sure hope not, and it seems unlikely: I expect very few, if
any, databases can handle multiple users sharing the same connection,
particularly with the normal concept of transactions (i.e. one transaction
open at one time on a connection). Therefore, it seems there must be some
locking mechanism within Zope to prevent this. Sharing a single connection
this way would tend to produce a serious bottleneck with 100+ users, but
of course, you can have multiple connections. How the application makes
use of these connections will affect the MySQL locking scheme to employ
somewhat.

Another question: How long (temporally) can a transaction exist? Is it
limited to a single HTTP request, or can it be maintained over several
requests? I'm suspecting the former.

-- 
andy dustman       |     programmer/analyst     |      comstar.net, inc.
telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d