[Zope] How to deal with MySQL's LAST_INSERT_ID() sensibly?

[Stefan Franke]

As far as I understand from the MySQL manuals, the value of LAST_INSERT_ID()
is maintained on a per-connection basis (wish it would be
per-connection/per-table;
a LAST_INSERT_ID(<table_name>) would make much more sense in combination
with
table locking, but anyway..).

I have a number of ZSqlMethods and Python methods that share a DB
connection.
I see two kinds of possible race conditions here:

1. Queries to different tables occur simultanously and affect the value of
   LAST_INSERT_ID(). This could propably be solved by creating multiple
   connections for each different kind of INSERT command.

2. Multiple requests trigger the same query which leads to confusion.

My question is if 2. is really a possible problem. I don't know enough about
database adapters and Zope threading. Are queries serialized by DB
connections
or by SQL Methods?

I wonder how others deal with that issue. Maybe I should renounce using
auto increment values at all, and maintain my own counters in the runtime
state. But how to make these thread safe?

Any ideas appreciated - please reply directly to me via email since I'm
not subscribed to the list.

Stefan

www.meso.net