How to deal with MySQL's LAST_INSERT_ID() sensibly?
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
+----[ 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|
participants (2)
-
Andrew Kenneth Milton -
Stefan Franke