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