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

[Andrew Kenneth Milton]

+----[ 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..).

It's per thread (MySQL thread).

| 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.

This can only happen if you have multiple threads, in which case
each thread will have its own last_insert_id. You should pull the
last_insert_id out when you do the insert (in the same ZSQL Method).

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

You need to have 

a) Multiple CPUs to handle multiple requests simultaneously, otherwise
   one will always 'win'. This also assumes you have a fairly good SMP OS,
   Linux isn't, so even with multiple CPUs I think you'll find it gets
   serialised by the kernel.

| 
| 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?

The DA determines it's level of concurrency, some DAs are not threadsafe
and therefore can only do one query at a time.

I think the other two levels are:-

Allow concurrent accesses for different connections -- requests to
different DB Connections are allowed in parallel.

Allow concurrent accesses across all connections (Oracle I think) --
all requests are handled in parallel.

-- 
Totally Holistic Enterprises Internet|  P:+61 7 3870 0066   | Andrew Milton
The Internet (Aust) Pty Ltd          |  F:+61 7 3870 4477   | 
ACN: 082 081 472 ABN: 83 082 081 472 |  M:+61 416 022 411   | Carpe Daemon
PO Box 837 Indooroopilly QLD 4068    |akm@theinternet.com.au|