On Jul 27, 2010, at 5:40 AM, Marek Stanisław Wawrzyczny wrote:
We now have a bit of discussion on how to handle PK generation in the new schema. My personal preference is auto incrementing PK columns. It certainly allows for a more generic approach to the handling of primary keys - it is not necessary for people to be aware of any additional infrastructure should records be inserted outside of any applications.
I am using auto-increment with MySQL (InnoDB/MyISAM). Haven't seen any problems so far (which may not mean much).
Others have expressed concern with MySQL handling of the LAST_INSERT_ID() functionality where there used to be (still is?) a problem with MySQL's threading implementation in high concurrency/ volume environments. (I presume this is precisely the mechanism used by Cayenne to populate IDs within the Entities?)
This mechanism is abstracted by MySQL JDBC driver, so Cayenne is unaware of it and is using standard JDBC API to get the keys. The underlying driver is based on LAST_INSERT_ID() of course. According to MySQL docs it should handle concurrent inserts just fine. Not sure if the reality is different. Googling only shows some pretty old complaints about concurrent inserts on MyISAM.
Andrus
