[Zope] SQL connection isolation / multiplexing

Zope Fiend zopefiend@netjunky.com
Tue, 5 Jun 2001 18:14:17 -0400


Hi everyone.  Hope this is the right place for this.

I have a data registration app running in zope that talks to a MySQL
database.

When a new piece of data is submitted, i need to generate a unique ID from
the database and then update several tables with that ID and other
information.  I went about this by making 2 SQL methods:

sql_register_1
 - inserts data into a table with an AUTO_INCREMENT field
sql_register_2
 - inserts data into a second table, calling LAST_INSERT_ID()

I know that SQL_LAST_INSERT() is connection-isolated in MySQL, i.e.
inserting into table 1 from one connection will not alter LAST_INSERT_ID()'s
value in another connection.

I don't know, however, how Zope handles Z MySQL Connections.  Looking at the
open connections to the DB server, and the running processes, I would guess
that each Zope child process maintains separate connections.

Does this mean that I am guaranteed to only have one parallel web request
per DB connection?
I am concerned that if two requests hit the server at the same time, the
following will happen:

Request 1: sql_register_1
Request 2: sql_register_1
Request 1: sql_register_2

If these go over separate DB connections, no problem.  If they don't,
kaboom.

Answers?

Thanks for any light.

~ZF