Yes, the drawback is if you keep getting conflicts (which hopefully would be minimal).
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, July 13, 2006 4:54 PM To: [email protected] Subject: Re: Duplicate Key Problem Hi, This might be a really random suggestion, but couldn't you use the following strategy, even with autocommit=true? public long getNextKey() { boolean conflict = true; while (conflict) { key = SELECT current_key_value from auto_pk_table next_key = key + increment; UPDATE auto_pk_table SET current_key_value = next_key_value WHERE current_key_value = key conflict = 0 rows updated } return next_key; } All you worry about here is making sure that your increment is large enough to avoid frequent conflicts. Craig On Jul 13, 2006, at 6:17 AM, Gentry, Michael (Contractor) wrote: > The basic strategy to refresh the primary key cache in MySQL is: > > * lock table > * select next key values from auto_pk_support > * update auto_pk_support with a new next key (20 keys by default) > * unlock table > > I was just looking at the MySQL docs for "LOCK TABLES" and it > appears to > work differently with InnoDB. Which DB storage type are you using? > Also, Andrus' suggestion that there could've been a failure with the > connection going down could be correct. It would've happened between > select and update above. An application got 20 PKs, but the > connection > was broken before it could update the PK table. Another application > comes along and gets the same 20 PKs. > > Any chance you can use PostgreSQL? It uses sequences which are atomic > operations. > > Thanks, > > /dev/mrg > > PS. Andrus: If a connection is broken and then Cayenne auto- > reconnects, > does it discard the PK cache? (I'm guessing not, which could explain > this.) > > > -----Original Message----- > From: Christian Mittendorf [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 13, 2006 6:23 AM > To: [email protected] > Subject: Re: Duplicate Key Problem > > > I haven't found any hint to something abnormal in my log files yet. > However, we experienced the duplicate key problem some minutes ago > and I was able to track down the problem a bit. > > Affected is only one single WebApp and these "Duplicat entry" errors > occured since yesterday, about 15:30. If I search for this exception > I can find this ID list: > > $ cat x.log.2006-07-12 x.log | grep 'message from server'|uniq > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048820' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048821' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048822' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048823' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048824' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048825' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048826' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048827' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048828' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048829' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048830' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048831' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048832' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048833' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048834' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048835' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048836' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048837' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048836' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048838' for key 1" > Caused by: java.sql.SQLException: null, message from server: > "Duplicate entry '17048839' for key 1" > > It seems as if one package of PKs (17048820 - 17048839 ) was double > used. > How does the PK magic of Cayenne work? Does each DataContext get its > own set of IDs? > > > Christian > > > > Am 12.07.2006 um 23:40 schrieb Andrus Adamchik: > >> Hmm.. MySQLPkGenerator runs "UNLOCK" in the finally block. Of >> course if the connection goes down or something else equally bad >> happens during unlock, then you can end up locking the table. I'll >> be curious to know what exactly happened, as we may improve the >> unlock reliability as a result. >> >> Andrus >> >> >> On Jul 12, 2006, at 5:29 PM, Christian Mittendorf wrote: >> >>> Yes, all of our applications on this database are cayenne based. >>> I will have a look at the logs tomorrow and see if I can find some >>> other exceptions... >>> >>> Thanks, >>> Christian >>> >>> Am 12.07.2006 um 19:56 schrieb Gentry, Michael ((Contractor)): >>> >>>> These could be related, then. I know the MySQL adapter has to >>>> lock the >>>> auto_pk_support table to generate keys and perhaps something >>>> happened to >>>> leave the table locked. Did you see any other exceptions? >>>> >>>> Also, you said you have multiple applications hitting the same >>>> database. >>>> Are all of these applications Cayenne-based? >>>> >>>> Thanks, >>>> >>>> /dev/mrg >>>> >>>> >>>> -----Original Message----- >>>> From: Christian Mittendorf [mailto:[EMAIL PROTECTED] >>>> Sent: Wednesday, July 12, 2006 1:50 PM >>>> To: [email protected] >>>> Subject: Re: Duplicate Key Problem >>>> >>>> >>>> We don't use the autoincrement from MySQL. >>>> >>>> Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)): >>>> >>>>> Are you using MySQL's autoincrement feature for the PKs or the >>>>> auto_pk_support table from Cayenne? >>>>> >>>>> -----Original Message----- >>>>> From: Christian Mittendorf >>>>> [mailto:[EMAIL PROTECTED] >>>>> Sent: Wednesday, July 12, 2006 4:29 AM >>>>> To: [email protected] >>>>> Subject: Duplicate Key Problem >>>>> >>>>> >>>>> Hello! >>>>> >>>>> We are running a MySQL 5.0 server using InnoDB tables for our >>>>> applications. We have multiple web applications accessing this >>>>> same >>>>> database and the system is running smooth and without problems >>>>> (almost). >>>>> >>>>> But yesterday we were experiencing some strange errors. During the >>>>> afternoon some CayenneRuntimeExceptions appeared in the log file, >>>>> which were caused by: >>>>> >>>>> Caused by: java.sql.SQLException: null, message from server: >>>>> "Duplicate entry '4353880' for key 1" >>>>> >>>>> A bit later other CayenneRuntimeException appeared. For those >>>>> exceptions the cause was: >>>>> >>>>> Caused by: java.sql.SQLException: Deadlock found when trying to >>>>> get >>>>> lock; Try restarting transaction, message from server: "Lock wait >>>>> timeout exceeded; try restarting transaction" >>>>> >>>>> I'm now wondering, are both errors related to each other? >>>>> >>>>> There are, from my point of view, two possible causes for the >>>>> "Duplicate entry..." message: >>>>> >>>>> - two Insert statements on the same object, which might be >>>>> possible >>>>> if the application is clustered, which our applications are >>>>> not, or >>>>> - dual use of the same key in different objects >>>>> >>>>> Has anybody else experienced something like that? Are there any >>>>> hints >>>>> what I might do to avoid such situations? >>>>> >>>>> Christian >>>>> >>>>> >>>>> >>>>> >>>> >>> >>> >> > Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!
