db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Debugging lock timeouts - versuchen01X
Date Wed, 03 Mar 2010 09:41:16 GMT
Gabriele Kahlout <gabriele@mysimpatico.com> 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

Mime
View raw message