Re: Fwd: Auto generation of database keys

[Satheesh Bandaram]

I thought Derby uses nested user transaction to commit generated identity values, to avoid holding locks. See getSetAutoincrementValue() in  org/apache/derby/impl/sql/execute/InsertResultSet.java. I tried the following using IJ, using default isolation levels. I couldn't see the problem. Satheesh ij> create table auto(i int generated always as identity, j int, k int); 0 rows inserted/updated/deleted ij> insert into auto(j, k) values (1,1); 1 row inserted/updated/deleted ij> select * from auto; I          |J          |K ----------------------------------- 1          |1          |1 1 row selected ij> autocommit off;                           <<<<<<<<<<<<< Start a transaction on CONNECTION0 ij>  insert into auto(j, k) values (2,2); 1 row inserted/updated/deleted ij> select * from auto; I          |J          |K ----------------------------------- 1          |1          |1 2          |2          |2 2 rows selected ij> connect 'tdb';                           <<<<<<<<<<<<<<<<  Get a second connection, CONNECTION1 ij(CONNECTION1)>  insert into auto(j, k) values (3,3); 1 row inserted/updated/deleted ij(CONNECTION1)> select * from auto with UR;                <<<<==== Use Uncommitted read isolation. I          |J          |K                                                                   <<<<===== Would block otherwise. ----------------------------------- 1          |1          |1 2          |2          |2 3          |3          |3 3 rows selected ij(CONNECTION1)> commit; ij(CONNECTION1)> set connection connection0;                <<<<<<<<==== Switch back to CONNECTION0 ij(CONNECTION0)> select * from auto; I          |J          |K ----------------------------------- 1          |1          |1 2          |2          |2 3          |3          |3 3 rows selected ij(CONNECTION0)> commit;                                     <<<<<<<<<<<<<<=== == COMMIT. ij(CONNECTION0)> select * from auto; I          |J          |K ----------------------------------- 1          |1          |1 2          |2          |2 3          |3          |3 3 rows selected ij(CONNECTION0)> Craig Russell wrote: 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!