Re: Auto generation of database keys

TomohitoNakayama Tue, 14 Jun 2005 06:24:42 -0700

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
         [EMAIL PROTECTED]
         [EMAIL PROTECTED]

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

----- Original Message -----

From: Craig Russell

To: [email protected]

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:[EMAIL PROTECTED]

P.S. A good JDO? O, Gasp!

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!

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

Re: Auto generation of database keys

Reply via email to