db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "TomohitoNakayama" <tomon...@basil.ocn.ne.jp>
Subject Re: Auto generation of database keys
Date Tue, 14 Jun 2005 13:25:45 GMT
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
         tomohito@rose.zero.ad.jp

         Naka
         http://www5.ocn.ne.jp/~tomohito/TopPage.html

*/
  ----- Original Message ----- 
  From: Craig Russell
  To: 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!



Mime
View raw message