From derby-user-return-12361-apmail-db-derby-user-archive=db.apache.org@db.apache.org Wed Mar 03 09:41:27 2010 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 42011 invoked from network); 3 Mar 2010 09:41:27 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 3 Mar 2010 09:41:27 -0000 Received: (qmail 80260 invoked by uid 500); 3 Mar 2010 09:41:20 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 80195 invoked by uid 500); 3 Mar 2010 09:41:19 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 80188 invoked by uid 99); 3 Mar 2010 09:41:19 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 03 Mar 2010 09:41:19 +0000 X-ASF-Spam-Status: No, hits=-4.0 required=10.0 tests=RCVD_IN_DNSWL_MED,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [192.18.6.21] (HELO gmp-eb-inf-1.sun.com) (192.18.6.21) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 03 Mar 2010 09:41:09 +0000 Received: from fe-emea-10.sun.com (gmp-eb-lb-1-fe1.eu.sun.com [192.18.6.7] (may be forged)) by gmp-eb-inf-1.sun.com (8.13.7+Sun/8.12.9) with ESMTP id o239emPn012973 for ; Wed, 3 Mar 2010 09:40:48 GMT MIME-version: 1.0 Content-transfer-encoding: 7BIT Content-type: text/plain; CHARSET=US-ASCII Received: from conversion-daemon.fe-emea-10.sun.com by fe-emea-10.sun.com (Sun Java(tm) System Messaging Server 7u2-7.04 64bit (built Jul 2 2009)) id <0KYP00K008XHKL00@fe-emea-10.sun.com> for derby-user@db.apache.org; Wed, 03 Mar 2010 09:40:47 +0000 (GMT) Received: from localhost ([unknown] [89.11.158.128]) by fe-emea-10.sun.com (Sun Java(tm) System Messaging Server 7u2-7.04 64bit (built Jul 2 2009)) with ESMTPSA id <0KYP00L409JPSS20@fe-emea-10.sun.com> for derby-user@db.apache.org; Wed, 03 Mar 2010 09:40:38 +0000 (GMT) Date: Wed, 03 Mar 2010 10:41:16 +0100 From: Knut Anders Hatlen Subject: Re: Debugging lock timeouts - versuchen01X In-reply-to: <1f5d398f1003030020i7c9ff727q1b9a4c02d1dc524d@mail.gmail.com> Sender: Knut.Hatlen@Sun.COM To: Derby Discussion Message-id: References: <1f5d398f1003011116n663ac9e1i151c26601215cf83@mail.gmail.com> <4B8C25C0.3050406@sun.com> <1f5d398f1003011332h71f88741i28037f54bfda687e@mail.gmail.com> <1f5d398f1003011334h5cec9f5qee13e93c418b9bdb@mail.gmail.com> <1f5d398f1003030020i7c9ff727q1b9a4c02d1dc524d@mail.gmail.com> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (usg-unix-v) X-Virus-Checked: Checked by ClamAV on apache.org Gabriele Kahlout writes: > Thank you for your answer. It solved it. > > (this is uncommitted code) > private static Connection getConnection(final boolean autoCommit) > throws Exception{ > Connection con = null; > try{ > con = DriverManager.getConnection("jdbc:default:connection"); > }catch(Exception e){ > con = SqlWrapper.connectToDerby(); > con.setAutoCommit(autoCommit); > } > return con; > } > > Now, I'd like to ask the following question: > > Executing this, in my try-catch algorithm, this insertion throws an > Exception. I then in the catch do things and the re-try it. However, > different from SQLite, the RID (PRIMARY AUTOINCREMENT) is not the same > as the previous, that failed. It seems that is wasted. > Maybe if one rollsback the transaction it returns? But that is > complicating in my atomic method. > Other solutions, other than live with this extra complexity, or modify the algo? > st.executeUpdate(SqlWrapper.insert(objTable, batColumn, batch)); Derby does not reset the auto-increment counter on failure or rollback, as documented here[1]: "Derby keeps track of the last increment value for a column in a cache. It also stores the value of what the next increment value will be for the column on disk in the AUTOINCREMENTVALUE column of the SYS.SYSCOLUMNS system table. Rolling back a transaction does not undo this value, and thus rolled-back transactions can leave "gaps" in the values automatically inserted into an identity column. Derby behaves this way to avoid locking a row in SYS.SYSCOLUMNS for the duration of a transaction and keeping concurrency high." [1] http://db.apache.org/derby/docs/10.5/ref/rrefsqlj37836.html If you want to avoid gaps in the identity values, perhaps you could use GENERATED BY DEFAULT AS IDENTITY instead of GENERATED ALWAYS AS IDENTITY, and set the RID value manually when you retry. -- Knut Anders