[Zope] Zope in Portals

[Andy Dustman]

On Tue, 28 Sep 1999, Christopher Petrilli wrote:

> > 3. It seemed to me that Zope doesn't support mySQL very well, being Oracle
> > mentioned all the time. As mySQL is still our main SQL platform, i ask if
> > mySQL DA is still being supported and/or developed?
> 
> Well, we have a bit of an architectural issue with MySQL... we're VERY VERY
> VERY focused on transaction processing, everything is a transaction in Zope,
> and the object database implements some very advanced ideas.  For
> application sanity, we expect all databases external to Zope to do the same,
> and MySQL obviously does not.  This has caused us to not develop for it,
> regardless of its popularity in certain areas.  It's simply incompatible
> with the architecture.

Here's the current line on transactions in MySQL from the 3.22.26a docs:

"""5.3.3 Transactions

Transactions are not supported. MySQL shortly will support atomic
operations, which are like transactions without rollback. With atomic
operations, you can execute a group of INSERT/SELECT/whatever commands and
be guaranteed that no other thread will interfere. In this
context, you won't usually need rollback. Currently, you can prevent
interference from other threads by using the LOCK TABLES and UNLOCK
TABLES commands. See section 7.23 LOCK TABLES/UNLOCK TABLES syntax. 
"""

I didn't know how the transactioning fit into the Database Adapator I just
pulled down ZPyGreSQLDA for an example. It appears the DB class which the
DA defines can override three internal methods:

    def _begin(self):
        self.db.query('begin transaction')

    def _finish(self):
        self.db.query('commit transaction')

    def _abort(self):
        self.db.query('rollback transaction')

(For a Python DB API-style interface, _begin would pass, _finish would do
self.db.commit(), and _abort would do self.db.rollback().)

There are a couple possibilities for implementing transactions in
ZMySQLDA:

1) Automatic use of LOCK TABLES.

In this case, the interface parses the SQL statements slightly to
determine what tables are being used, and issues an appropriate LOCK
TABLES command. SELECT statements would cause a READ lock to be
established and the statement to be executed. INSERT/REPLACE/UPDATE
statements would not be executed immediately but pushed into a queue, and
a WRITE lock would be established. _finish() would cause these statements
to be executed and UNLOCK TABLES. _abort() would flush the queue. (REPLACE
is a MySQL-specific statement, same as INSERT but can overwrite existing
data.)

2) Automatic use of GET_LOCK().

MySQL also has the GET_LOCK(lockname)/RELEASE_LOCK(lockname) pair. Only
thread can have a lock on lockname at once. The lockname could simply be
"ZOPE" which would allow one process to have an open transaction at once
(safe but slow), or there could be some user-refinement.

I think #1 is feasible, and has the best possibility for decent
performance. #2 seems certain to work, though the performance may not be
so good. Even #1 will tend to LOCK TABLES when it doesn't have to: If you
are executing a single SQL statement, it's probably unnecessary.

Some more MySQL info at http://www.zope.org/Members/adustman/MySQLdb

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