[Zope] SQL connection isolation / multiplexing

[Jason Byron]

I'm not sure about the details myself, but I would
change your sql_register_1 to something like this:

insert into ...
<dtml-var sql_delimiter>
select last_insert_id() as lastinsertid

then your insert also gives the new id (as lastinsertid)
and I think you don't have to worry about the timing.

hth,
jason


--- Zope Fiend <zopefiend@netjunky.com> wrote:
> 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
> 
> 
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists - 
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35 
a year!  http://personal.mail.yahoo.com/