SQL connection isolation / multiplexing
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
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/
participants (2)
-
Jason Byron -
Zope Fiend