Re: Auto generation of database keys
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/deletedij select * from test;VALUE---1 1 row selectedij rollback;ij select * from test;VALUE--- 0 rows selectedij insert into test(value) values(default);1 row inserted/updated/deletedij select * from test;VALUE---2 Well ... Key generationis nontransactional already ;) Taking asidemy joke, I think there exists room to optimize ... I will filethis 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: 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 asDATASTORE_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 internalsynchronization 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.ac40348015x0104x675cxbca4xdab5f0bf0.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
Re: Fwd: Auto generation of database keys
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 asDATASTORE_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 internalsynchronization 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.ac40348015x0104x675cxbca4xdab5f0bf0.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)
Re: Auto generation of database keys
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 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Naka http://www5.ocn.ne.jp/~tomohito/TopPage.html */ - Original Message - *From:* Craig Russell mailto:[EMAIL PROTECTED] *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.ac40348015x0104x675cxbca4xdab5f0bf0.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
Re: Fwd: Auto generation of database keys
I have not been able to reproduce this issue. Derby uses nested internal transactions when updating the IDENTITY column, it does not try to guarantee that a value won't be lost. These locks should not be held until end of user transaction, they are committed separate from the user transaction, so the lock is held for the time it takes to update the system catalog row and commit the internal transaction. There are some user level things the other transaction could be doing to block this: o other transaction did ddl on this table and has not committed. o other transaction did some sort of metadata select either explicitly against derby system catalog or through jdbc metadata call. I assume you have not set any of the lock timeout properties, it is definitely possible to block on this lock for a short time so setting locktimeout to -1 will also cause this issue, but default timeout should not be happening. The best 1st step to debugging locking issues is to use the properties to get the system to dump out more information about the lock table when you encounter the lock timeout. Try setting derby.locks.monitor=true. In this case the interesting information is what locks are being held by the other transaction blocking this one. On a side note, what jvm/derby version are you using. Getting stack traces with line numbers can help out a lot in debugging this stuff. I don't know if I get them because I am using a development build, or if it is the jvm environment I have. This should have nothing to do with the lock timeout, just makes it easier to diagnose the issue. 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.ac40348015x0104x675cxbca4xdab5f0bf0.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
Re: Fwd: Auto generation of database keys
To help debug this, try setting both derby.locks.monitor=true and derby.locks.deadlockTrace=true . This will print the lock table dump when the timeout or deadlock happens to derby.log. The lock table will give information on what locks are being held etc.. Sunitha. Mike Matrigali wrote: I have not been able to reproduce this issue. Derby uses nested internal transactions when updating the IDENTITY column, it does not try to guarantee that a value won't be lost. These locks should not be held until end of user transaction, they are committed separate from the user transaction, so the lock is held for the time it takes to update the system catalog row and commit the internal transaction. There are some user level things the other transaction could be doing to block this: o other transaction did ddl on this table and has not committed. o other transaction did some sort of metadata select either explicitly against derby system catalog or through jdbc metadata call. I assume you have not set any of the lock timeout properties, it is definitely possible to block on this lock for a short time so setting locktimeout to -1 will also cause this issue, but default timeout should not be happening. The best 1st step to debugging locking issues is to use the properties to get the system to dump out more information about the lock table when you encounter the lock timeout. Try setting derby.locks.monitor=true. In this case the interesting information is what locks are being held by the other transaction blocking this one. On a side note, what jvm/derby version are you using. Getting stack traces with line numbers can help out a lot in debugging this stuff. I don't know if I get them because I am using a development build, or if it is the jvm environment I have. This should have nothing to do with the lock timeout, just makes it easier to diagnose the issue. 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.ac40348015x0104x675cxbca4xdab5f0bf0.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
Re: Auto generation of database keys
Hi Mike,Thanks for the info. It looks like I'll have to create a JDBC test case. I'll let you know when I have a test case that I can show the problem on.Currently the test is a JUnit test that is written to access Derby through JDBC, JDO, and JPOX, so it's not exactly an easy case to reproduce in a small environment. I was hoping to get some ideas without having to construct a JDBC test case.Thanks,CraigOn Jun 14, 2005, at 12:29 PM, [EMAIL PROTECTED] wrote:From: Mike Matrigali [EMAIL PROTECTED] Date: June 14, 2005 10:45:16 AM PDT To: Derby Development derby-dev@db.apache.org Subject: Re: Fwd: Auto generation of database keys I have not been able to reproduce this issue. Derby uses nested internal transactions when updating the IDENTITY column, it does not try to guarantee that a value won't be lost. These locks should not be held until end of user transaction, they are committed separate from the user transaction, so the lock is held for the time it takes to update the system catalog row and commit the internal transaction. There are some user level things the other transaction could be doing to block this: o other transaction did ddl on this table and has not committed. o other transaction did some sort of metadata select either explicitly against derby system catalog or through jdbc metadata call. I assume you have not set any of the lock timeout properties, it is definitely possible to block on this lock for a short time so setting locktimeout to -1 will also cause this issue, but default timeout should not be happening. The best 1st step to debugging locking issues is to use the properties to get the system to dump out more information about the lock table when you encounter the lock timeout. Try setting derby.locks.monitor=true. In this case the interesting information is what locks are being held by the other transaction blocking this one. On a side note, what jvm/derby version are you using. Getting stack traces with line numbers can help out a lot in debugging this stuff. I don't know if I get them because I am using a development build, or if it is the jvm environment I have. This should have nothing to do with the lock timeout, just makes it easier to diagnose the issue. 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.ac40348015x0104x675cxbca4xdab5f0bf0.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
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(StandardException.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.java) [java] at org.apache.derby.impl.store.raw.xact.RowLocking3.lockRecordForWrite(RowLocking3.java) [java] at org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.lockPositionForWrite(OpenConglomerate.java) [java] at org.apache.derby.impl.store.access.conglomerate.GenericConglomerateController.fetch(GenericConglomerateController.java) [java] at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getSetAutoincrementValue(DataDictionaryImpl.java) [java] at org.apache.derby.impl.sql.execute.InsertResultSet.getSetAutoincrementValue(InsertResultSet.java) [java] at org.apache.derby.impl.sql.execute.BaseActivation.getSetAutoincrementValue(BaseActivation.java) [java] at org.apache.derby.exe.ac40348015x0104x675cxbca4xdab5f0bf0.e0(Unknown Source) [java] at org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGeneratedClass.java) [java] at org.apache.derby.impl.sql.execute.RowResultSet.getNextRowCore(RowResultSet.java) [java] at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(NormalizeResultSet.java) [java] at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java) [java] at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java) [java] at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java) [java] at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java) [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java) [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java) [java] at org.jpox.store.rdbms.request.Request.executeUpdate(Request.java:69) [java] at org.jpox.store.rdbms.request.InsertRequest.execute(InsertRequest.java:253) [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:2913)Am I misusing the key generation? Can I get nontransactional key generation?Thanks,CraigCraig RussellArchitect, Sun Java Enterprise System http://java.sun.com/products/jdo408 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! smime.p7s Description: S/MIME cryptographic signature
