db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Auto generation of database keys
Date Tue, 14 Jun 2005 17:26:12 GMT
The implementation of identity values is transactional in that it
uses transactions to guarantee no 2 rows are assigned the same
number.  It does not guarantee that there won't be lost values,
nor does it guarantee any particular order (though the implementation
does allocated the numbers in order - I believe the only thing an
application can assume is some sort of unique value.

TomohitoNakayama wrote:

> Hello.
>  
> I experimented ....
>  
> ij> create table test(value int generated always as identity);
> 0 rows inserted/updated/deleted
> ij> insert into test(value) values(default);
> 1 row inserted/updated/deleted
> ij> select * from test;
> VALUE
> -----------
> 1
>  
> 1 row selected
> ij> rollback;
> ij> select * from test;
> VALUE
> -----------
>  
> 0 rows selected
> ij> insert into test(value) values(default);
> 1 row inserted/updated/deleted
> ij> select * from test;
> VALUE
> -----------
> 2
>  
>  
> Well ...
> Key generation is nontransactional already ;)
>  
> Taking aside my joke,
> I think there exists room to optimize ...
> I will file this into JIRA ...
>  
>  
> Best regards.
>  
>  
> /*
>  
>          Tomohito Nakayama
>          tomonaka@basil.ocn.ne.jp <mailto:tomonaka@basil.ocn.ne.jp>
>          tomohito@rose.zero.ad.jp <mailto:tomohito@rose.zero.ad.jp>
>  
>          Naka
>          http://www5.ocn.ne.jp/~tomohito/TopPage.html
>  
> */
> 
>     ----- Original Message -----
>     *From:* Craig Russell <mailto:Craig.Russell@Sun.COM>
>     *To:* derby-dev@db.apache.org <mailto:derby-dev@db.apache.org>
>     *Sent:* Tuesday, June 14, 2005 8:42 AM
>     *Subject:* Fwd: Auto generation of database keys
> 
>     Hi,
> 
>     I'm running into a locking issue when using generated keys. My
>     primary key column is defined as DATASTORE_IDENTITY BIGINT NOT NULL
>     GENERATED ALWAYS AS IDENTITY. I don't care about the key being
>     transactional. That is, if a transaction rolls back, I can live with
>     the key that was allocated being permanently unused.
> 
>     My application has two transactions inserting rows into the same
>     table, and the threads have internal synchronization such that I
>     need to have both insert statements succeed independently. The
>     isolation level is the default. 
> 
>     When I run the transactions, I get a timeout exception indicating
>     that only one of the transactions can get an autogenerated key and
>     the other has to wait until the first transaction commits. This
>     stack trace is from the transaction that is waiting for the first
>     transaction to commit.
> 
>          [java] ERROR 40XL1: A lock could not be obtained within the
>     time requested
>          [java]      at
>     org.apache.derby.iapi.error.StandardException.newException(StandardExcep
>     tion.java)
>          [java]      at
>     org.apache.derby.impl.services.locks.LockSet.lockObject(LockSet.java)
>          [java]      at
>     org.apache.derby.impl.services.locks.SinglePool.lockAnObject(SinglePool.
>     java)
>          [java]      at
>     org.apache.derby.impl.services.locks.SinglePool.lockObject(SinglePool.ja
>     va)
>          [java]      at
>     org.apache.derby.impl.store.raw.xact.RowLocking3.lockRecordForWrite(RowL
>     ocking3.java)
>          [java]      at
>     org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.lockPos
>     itionForWrite(OpenConglomerat
>     e.java)
>          [java]      at
>     org.apache.derby.impl.store.access.conglomerate.GenericConglomerateContr
>     oller.fetch(GenericConglomera
>     teController.java)
>          [java]      at
>     org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getSetAutoincrement
>     Value(DataDictionaryImpl.java
>     )
>          [java]      at
>     org.apache.derby.impl.sql.execute.InsertResultSet.getSetAutoincrementVal
>     ue(InsertResultSet.java)
>          [java]      at
>     org.apache.derby.impl.sql.execute.BaseActivation.getSetAutoincrementValu
>     e(BaseActivation.java)
>          [java]      at
>     org.apache.derby.exe.ac40348015x0104x675cxbca4xffffdab5f0bf0.e0(Unknown
>     Source)
>          [java]      at
>     org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGenerate
>     dClass.java)
>          [java]      at
>     org.apache.derby.impl.sql.execute.RowResultSet.getNextRowCore(RowResultS
>     et.java)
>          [java]      at
>     org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(Norm
>     alizeResultSet.java)
>          [java]      at
>     org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWr
>     iteResultSet.java)
>          [java]      at
>     org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.j
>     ava)
>          [java]      at
>     org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPrepar
>     edStatement.java)
>          [java]      at
>     org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatemen
>     t.java)
>          [java]      at
>     org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Embed
>     PreparedStatement.java)
>          [java]      at
>     org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPre
>     paredStatement.java)
>          [java]      at
>     org.jpox.store.rdbms.request.Request.executeUpdate(Request.java:69)
>          [java]      at
>     org.jpox.store.rdbms.request.InsertRequest.execute(InsertRequest.java:25
>     3)
>          [java]      at
>     org.jpox.store.rdbms.table.ClassTable.insert(ClassTable.java:1673)
>          [java]      at
>     org.jpox.store.StoreManager.insert(StoreManager.java:634)
>          [java]      at
>     org.jpox.state.StateManagerImpl.internalMakePersistent(StateManagerImpl.
>     java:2940)
>          [java]      at
>     org.jpox.state.StateManagerImpl.makePersistent(StateManagerImpl.java:291
>     3)
> 
> 
>     Am I misusing the key generation? Can I get nontransactional key
>     generation?
> 
>     Thanks,
> 
>     Craig
> 
>     Craig Russell
>     Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
>     408 276-5638 mailto:Craig.Russell@sun.com
>     P.S. A good JDO? O, Gasp!
> 
> 
>     Craig Russell
> 
>     Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 
>     408 276-5638 mailto:Craig.Russell@sun.com
> 
>     P.S. A good JDO? O, Gasp!
> 
> 
> 
> ------------------------------------------------------------------------
> 
> No virus found in this outgoing message.
> Checked by AVG Anti-Virus.
> Version: 7.0.323 / Virus Database: 267.6.9 - Release Date: 2005/06/11

Mime
View raw message