[Zope] Zope in Portals

[Christopher Petrilli]

>> 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).

SELECT FOR UPDATE is an idiom for telling the database that you plan to use
the data for an UPDATE at some point, so that it becomes locked
(row/table/column, whatever).  This is tightly wound with the concept of
transactions, and I wouldn't expect MySQL to have it.

>> 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.)

As you'll note, I specifically talked about heavy update situations.
Anything can be fast and safe if it's read-only ;-)

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

I believe it's unrealistic to expect someone to have to clean up after
inadequacies in the database model.  You COULD say "you've got to lock the
table before you do anything with it", but then you're loosing all the
benefits of hiding database ideas under OO concepts.  One of the great
things about using ZSQLMethods as they are inteded is you can reconnect to
multiple different databases without rewriting anything if you stick to SQL
standard syntax.  If you have to insert all kinds of things, then well, it's
not logical.

Regardless, even if you did LOCK the table, that doesn't let you rollback
changes that were done in the event of a later failure.  This is very common
in database applications of any complexity.

> Incidentally, the "+100 users comment" raises the question: Will Zope
> allow the same database connection to be used simultaneously in more than
> one thread?

Each thread can have a connection to each database that is accessed, but
connections are not shared between threads in the way you are hypothesizing.
We are talking of Level 3 DAs, those that are fully thread-safe, which the
MySQL DA is not.  It must be run in a single thread.  With Oracle for
example, if you have 5 application threads, you could theoretically have 5
Oracle connections.

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

Generally the former, but it might cross many many methods in the process,
and start many many transactions in different sources that are then commited
as a whole (using a core two-phase protocol).

Chris

--
| Christopher Petrilli        Python Powered        Digital Creations, Inc.
| petrilli@digicool.com                             http://www.digicool.com