On Fri, 9 Jul 1999, Rob Page wrote:

...

Anybody know how well MySQL performs with multiple threads? Last I heard, it serializes database calls, which isn't exactly promising.

MySQL doesn't support transactions. Chris Petrilli here has the details but I'm led to believe this is a fatal blow to MySQL's ability to _ever_ (at least until it does support txns) reliably support threaded usage.

The MySQL solution to doing transactions is to instead do table locking. At least, this is what the docs say (see section 5.4). By not supporting transactions, this makes the database 2-3x faster (claimed). An exerpt: """ The current problem is actually ROLLBACK. Without ROLLBACK, you can do any kind of COMMIT action with LOCK TABLES. To support ROLLBACK, MySQL would have to be changed to store all old records that were updated and revert everything back to the starting point if ROLLBACK was issued. For simple cases, this isn't that hard to do (the current isamlog could be used for this purpose), but it would be much more difficult to implement ROLLBACK for ALTER/DROP/CREATE TABLE. To avoid using ROLLBACK, you can use the following strategy: 1.Use LOCK TABLES ... to lock all the tables you want to access. 2.Test conditions. 3.Update if everything is okay. 4.Use UNLOCK TABLES to release your locks. This is usually a much faster method than using transactions with possible ROLLBACKs, although not always. The only situation this solution doesn't handle is when someone kills the threads in the middle of an update. In this case, all locks will be released but some of the updates may not have been executed.""" But obviously, if you need transactions, or need to write a cross-database-platform app, don't use MySQL. I have heard from someone who has benchmarked MySQL against Informix (on Linux) that MySQL is about 2x faster, if the queries are simple. The more complicated the queries are, the smaller this difference becomes. Anyway, back to the original question: In general, MySQL does not serialize database calls. With the non-standard LOW_PRIORITY keyword on INSERT/REPLACE/UPDATE statements (REPLACE is like INSERT, except pre-existing rows matching the primary key are replaced and there is no error), the write is delayed until no other client is reading from the table. With the non-standard DELAYED keyword on INSERT/REPLACE statements, the query returns immediately but the data is not written out until later; it goes into a delayed queue (with it's own thread). The delayed queue handler tries to write delayed rows out en masse. This is intended for things like logging where a lot of records are written on a regular basis, but you don't want to delay the client. Internally, the MySQL server uses table locks between server threads. The client libraries are thread-safe, but there are some subtle caveats about the connect call. I mentioned this (buried in another thread), but I have a MySQLdb module for Python that is completely thread-safe (i.e. it releases the global interpreter lock on blocking calls). It's mostly compatible with MySQLmodule-1.4 (what ZMySQLDA uses) so it should be easily adaptable to Zope, except for the fact that it does try to use mxDateTime, which conflicts name-wise with Zope's DateTime. If any changes are required to make it work with Zope (it should probably have it's own ZMySQLdbDA), let me know. http://starship.python.net/crew/adustman/MySQLdb.html -- andy dustman | programmer/analyst | comstar communications corporation telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d