...

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