Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 31686 invoked from network); 1 Mar 2010 21:35:25 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 1 Mar 2010 21:35:25 -0000 Received: (qmail 13902 invoked by uid 500); 1 Mar 2010 21:35:23 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 13875 invoked by uid 500); 1 Mar 2010 21:35:23 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 13868 invoked by uid 99); 1 Mar 2010 21:35:23 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 01 Mar 2010 21:35:23 +0000 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [209.85.220.209] (HELO mail-fx0-f209.google.com) (209.85.220.209) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 01 Mar 2010 21:35:13 +0000 Received: by fxm1 with SMTP id 1so3514437fxm.15 for ; Mon, 01 Mar 2010 13:34:53 -0800 (PST) MIME-Version: 1.0 Received: by 10.223.77.66 with SMTP id f2mr5600593fak.84.1267479292777; Mon, 01 Mar 2010 13:34:52 -0800 (PST) X-Originating-IP: [193.206.186.101] In-Reply-To: <1f5d398f1003011332h71f88741i28037f54bfda687e@mail.gmail.com> References: <1f5d398f1003011116n663ac9e1i151c26601215cf83@mail.gmail.com> <4B8C25C0.3050406@sun.com> <1f5d398f1003011332h71f88741i28037f54bfda687e@mail.gmail.com> Date: Mon, 1 Mar 2010 22:34:52 +0100 Message-ID: <1f5d398f1003011334h5cec9f5qee13e93c418b9bdb@mail.gmail.com> Subject: Re: Debugging lock timeouts - versuchen01X From: Gabriele Kahlout To: Derby Discussion Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Checked: Checked by ClamAV on apache.org java.sql.SQLException: The exception 'java.sql.SQLException: A lock could not be obtained within the time requested. The lockTable dump is: 2010-03-01 21:19:01.316 GMT XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE / LOCKOBJ |INDEXNAME / CONTAINER_ID / (MODE for LATCH only) |TABLENAME / CONGLOM_ID | ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------- *** The following row is the victim *** 248 |ROW |S |0 |(4,21) |WAIT |S |NULL |SYSCOLUMNS | *** The above row is the victim *** 202 |ROW |S |4 |(1,7) |GRANT|S |NULL |SYSCONSTRAINTS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |OBJECTS | 202 |ROW |X |1 |(1,8) |GRANT|T |NULL |OBJECTS | 202 |TABLE |IX |8 |Tablelock |GRANT|T |NULL |EXPRESSIONS | 202 |TABLE |IX |6 |Tablelock |GRANT|T |NULL |OBJECTS | 248 |TABLE |IS |2 |Tablelock |GRANT|S |NULL |SYSSCHEMAS | 202 |ROW |X |1 |(1,8) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,8) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |BATCHES | 202 |TABLE |IS |16 |Tablelock |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IX |1 |Tablelock |GRANT|S |NULL |SYSCOLUMNS | 248 |TABLE |IS |2 |Tablelock |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |X |1 |(1,10) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,9) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |S |4 |(4,20) |GRANT|S |NULL |SYSCOLUMNS | 248 |ROW |S |2 |(4,20) |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IS |4 |Tablelock |GRANT|S |NULL |SYSKEYS | 202 |ROW |S |4 |(4,21) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |X |2 |(4,21) |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IS |12 |Tablelock |GRANT|S |NULL |SYSCONGLOMERATES | 202 |TABLE |S |2 |Tablelock |GRANT|S |NULL |SYSCONGLOMERATES | 202 |TABLE |IS |12 |Tablelock |GRANT|S |NULL |SYSTABLES | 248 |TABLE |IS |2 |Tablelock |GRANT|S |NULL |SYSTABLES | 248 |ROW |S |2 |(1,17) |GRANT|S |NULL |SYSSCHEMAS | 202 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |BATCHES | 202 |ROW |S |2 |(4,31) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,30) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(1,30) |GRANT|S |NULL |SYSTABLES | 202 |ROW |S |2 |(4,29) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(1,29) |GRANT|S |NULL |SYSTABLES | 202 |ROW |S |4 |(5,17) |GRANT|S |NULL |SYSCONGLOMERATES | 202 |ROW |S |4 |(1,7) |GRANT|S |NULL |SYSKEYS | 202 |ROW |S |2 |(4,28) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,27) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,26) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,25) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,24) |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IS |4 |Tablelock |GRANT|S |NULL |SYSCONSTRAINTS | 202 |ROW |S |4 |(4,23) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |4 |(1,28) |GRANT|S |NULL |SYSTABLES | 202 |ROW |S |4 |(5,16) |GRANT|S |NULL |SYSCONGLOMERATES | 202 |ROW |S |4 |(4,22) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |4 |(1,27) |GRANT|S |NULL |SYSTABLES | 248 |ROW |S |2 |(1,27) |GRANT|S |NULL |SYSTABLES | 202 |TABLE |IX |1 |Tablelock |GRANT|T |NULL |REFERSTO | ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------' was thrown while evaluating an expression. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknow= n Source) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.seeNextException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(U= nknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unkn= own Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Sour= ce) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Sour= ce) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Sour= ce) at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(Unknown Source) at com.mysimpatico.memorizeasy.engine.Database.insertWords(Database.java:4= 26) at com.mysimpatico.memorizeasy.engine.Database.insertWords(Database.java:4= 24) at com.mysimpatico.memorizeasy.engine.Database.insertWords(Database.java:4= 61) at com.mysimpatico.memorizeasy.engine.Database.insertWords(Database.java:2= 90) at com.mysimpatico.memorizeasy.engine.Database.importWords(Database.java:2= 96) at com.mysimpatico.memorizeasy.test.DatabaseTest.testImportWords(DatabaseT= est.java:118) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.ja= va:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccesso= rImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMetho= dRunner.java:99) at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRu= nner.java:81) at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndA= fterRunner.java:34) at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.= java:75) at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:4= 5) at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(Test= ClassMethodsRunner.java:66) at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsR= unner.java:35) at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRu= nner.java:42) at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndA= fterRunner.java:34) at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4Te= stReference.java:45) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.j= ava:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteT= estRunner.java:460) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteT= estRunner.java:673) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRu= nner.java:386) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestR= unner.java:196) Caused by: java.sql.SQLException: The exception 'java.sql.SQLException: A lock could not be obtained within the time requested. The lockTable dump is: 2010-03-01 21:19:01.316 GMT XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE / LOCKOBJ |INDEXNAME / CONTAINER_ID / (MODE for LATCH only) |TABLENAME / CONGLOM_ID | ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------- *** The following row is the victim *** 248 |ROW |S |0 |(4,21) |WAIT |S |NULL |SYSCOLUMNS | *** The above row is the victim *** 202 |ROW |S |4 |(1,7) |GRANT|S |NULL |SYSCONSTRAINTS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |OBJECTS | 202 |ROW |X |1 |(1,8) |GRANT|T |NULL |OBJECTS | 202 |TABLE |IX |8 |Tablelock |GRANT|T |NULL |EXPRESSIONS | 202 |TABLE |IX |6 |Tablelock |GRANT|T |NULL |OBJECTS | 248 |TABLE |IS |2 |Tablelock |GRANT|S |NULL |SYSSCHEMAS | 202 |ROW |X |1 |(1,8) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,8) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |BATCHES | 202 |TABLE |IS |16 |Tablelock |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IX |1 |Tablelock |GRANT|S |NULL |SYSCOLUMNS | 248 |TABLE |IS |2 |Tablelock |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |X |1 |(1,10) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,9) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |S |4 |(4,20) |GRANT|S |NULL |SYSCOLUMNS | 248 |ROW |S |2 |(4,20) |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IS |4 |Tablelock |GRANT|S |NULL |SYSKEYS | 202 |ROW |S |4 |(4,21) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |X |2 |(4,21) |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IS |12 |Tablelock |GRANT|S |NULL |SYSCONGLOMERATES | 202 |TABLE |S |2 |Tablelock |GRANT|S |NULL |SYSCONGLOMERATES | 202 |TABLE |IS |12 |Tablelock |GRANT|S |NULL |SYSTABLES | 248 |TABLE |IS |2 |Tablelock |GRANT|S |NULL |SYSTABLES | 248 |ROW |S |2 |(1,17) |GRANT|S |NULL |SYSSCHEMAS | 202 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |BATCHES | 202 |ROW |S |2 |(4,31) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,30) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(1,30) |GRANT|S |NULL |SYSTABLES | 202 |ROW |S |2 |(4,29) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(1,29) |GRANT|S |NULL |SYSTABLES | 202 |ROW |S |4 |(5,17) |GRANT|S |NULL |SYSCONGLOMERATES | 202 |ROW |S |4 |(1,7) |GRANT|S |NULL |SYSKEYS | 202 |ROW |S |2 |(4,28) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,27) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,26) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,25) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,24) |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IS |4 |Tablelock |GRANT|S |NULL |SYSCONSTRAINTS | 202 |ROW |S |4 |(4,23) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |4 |(1,28) |GRANT|S |NULL |SYSTABLES | 202 |ROW |S |4 |(5,16) |GRANT|S |NULL |SYSCONGLOMERATES | 202 |ROW |S |4 |(4,22) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |4 |(1,27) |GRANT|S |NULL |SYSTABLES | 248 |ROW |S |2 |(1,27) |GRANT|S |NULL |SYSTABLES | 202 |TABLE |IX |1 |Tablelock |GRANT|T |NULL |REFERSTO | ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------' was thrown while evaluating an expression. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAc= rossDRDA(Unknown Source) ... 36 more Caused by: java.sql.SQLException: A lock could not be obtained within the time requested. The lockTable dump is: 2010-03-01 21:19:01.316 GMT XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE / LOCKOBJ |INDEXNAME / CONTAINER_ID / (MODE for LATCH only) |TABLENAME / CONGLOM_ID | ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------- *** The following row is the victim *** 248 |ROW |S |0 |(4,21) |WAIT |S |NULL |SYSCOLUMNS | *** The above row is the victim *** 202 |ROW |S |4 |(1,7) |GRANT|S |NULL |SYSCONSTRAINTS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |OBJECTS | 202 |ROW |X |1 |(1,8) |GRANT|T |NULL |OBJECTS | 202 |TABLE |IX |8 |Tablelock |GRANT|T |NULL |EXPRESSIONS | 202 |TABLE |IX |6 |Tablelock |GRANT|T |NULL |OBJECTS | 248 |TABLE |IS |2 |Tablelock |GRANT|S |NULL |SYSSCHEMAS | 202 |ROW |X |1 |(1,8) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,8) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |BATCHES | 202 |TABLE |IS |16 |Tablelock |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IX |1 |Tablelock |GRANT|S |NULL |SYSCOLUMNS | 248 |TABLE |IS |2 |Tablelock |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |X |1 |(1,10) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,9) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |S |4 |(4,20) |GRANT|S |NULL |SYSCOLUMNS | 248 |ROW |S |2 |(4,20) |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IS |4 |Tablelock |GRANT|S |NULL |SYSKEYS | 202 |ROW |S |4 |(4,21) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |X |2 |(4,21) |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IS |12 |Tablelock |GRANT|S |NULL |SYSCONGLOMERATES | 202 |TABLE |S |2 |Tablelock |GRANT|S |NULL |SYSCONGLOMERATES | 202 |TABLE |IS |12 |Tablelock |GRANT|S |NULL |SYSTABLES | 248 |TABLE |IS |2 |Tablelock |GRANT|S |NULL |SYSTABLES | 248 |ROW |S |2 |(1,17) |GRANT|S |NULL |SYSSCHEMAS | 202 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |BATCHES | 202 |ROW |S |2 |(4,31) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,30) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(1,30) |GRANT|S |NULL |SYSTABLES | 202 |ROW |S |2 |(4,29) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(1,29) |GRANT|S |NULL |SYSTABLES | 202 |ROW |S |4 |(5,17) |GRANT|S |NULL |SYSCONGLOMERATES | 202 |ROW |S |4 |(1,7) |GRANT|S |NULL |SYSKEYS | 202 |ROW |S |2 |(4,28) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,27) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,26) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,25) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,24) |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IS |4 |Tablelock |GRANT|S |NULL |SYSCONSTRAINTS | 202 |ROW |S |4 |(4,23) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |4 |(1,28) |GRANT|S |NULL |SYSTABLES | 202 |ROW |S |4 |(5,16) |GRANT|S |NULL |SYSCONGLOMERATES | 202 |ROW |S |4 |(4,22) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |4 |(1,27) |GRANT|S |NULL |SYSTABLES | 248 |ROW |S |2 |(1,27) |GRANT|S |NULL |SYSTABLES | 202 |TABLE |IX |1 |Tablelock |GRANT|T |NULL |REFERSTO | ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------- at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAc= rossDRDA(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknow= n Source) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(U= nknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unkn= own Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Sour= ce) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Sour= ce) at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source) at com.mysimpatico.memorizeasy.engine.Database.refExpTablesComp(Database.j= ava:171) at org.apache.derby.exe.ac4ac48095x0127x1b97xa6d9x000000652bb04.g0(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.ja= va:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccesso= rImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(Unknown Sou= rce) at org.apache.derby.impl.sql.execute.CallStatementResultSet.open(Unknown Source) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source) at org.apache.derby.impl.sql.GenericPreparedStatement.executeSubStatement(= Unknown Source) at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unk= nown Source) at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknow= n Source) at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unk= nown Source) at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unkn= own Source) at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Sour= ce) ... 29 more Caused by: ERROR 40XL2: A lock could not be obtained within the time requested. The lockTable dump is: 2010-03-01 21:19:01.316 GMT XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE / LOCKOBJ |INDEXNAME / CONTAINER_ID / (MODE for LATCH only) |TABLENAME / CONGLOM_ID | ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------- *** The following row is the victim *** 248 |ROW |S |0 |(4,21) |WAIT |S |NULL |SYSCOLUMNS | *** The above row is the victim *** 202 |ROW |S |4 |(1,7) |GRANT|S |NULL |SYSCONSTRAINTS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |OBJECTS | 202 |ROW |X |1 |(1,8) |GRANT|T |NULL |OBJECTS | 202 |TABLE |IX |8 |Tablelock |GRANT|T |NULL |EXPRESSIONS | 202 |TABLE |IX |6 |Tablelock |GRANT|T |NULL |OBJECTS | 248 |TABLE |IS |2 |Tablelock |GRANT|S |NULL |SYSSCHEMAS | 202 |ROW |X |1 |(1,8) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,8) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,7) |GRANT|T |NULL |BATCHES | 202 |TABLE |IS |16 |Tablelock |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IX |1 |Tablelock |GRANT|S |NULL |SYSCOLUMNS | 248 |TABLE |IS |2 |Tablelock |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |X |1 |(1,10) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |X |1 |(1,9) |GRANT|T |NULL |EXPRESSIONS | 202 |ROW |S |4 |(4,20) |GRANT|S |NULL |SYSCOLUMNS | 248 |ROW |S |2 |(4,20) |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IS |4 |Tablelock |GRANT|S |NULL |SYSKEYS | 202 |ROW |S |4 |(4,21) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |X |2 |(4,21) |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IS |12 |Tablelock |GRANT|S |NULL |SYSCONGLOMERATES | 202 |TABLE |S |2 |Tablelock |GRANT|S |NULL |SYSCONGLOMERATES | 202 |TABLE |IS |12 |Tablelock |GRANT|S |NULL |SYSTABLES | 248 |TABLE |IS |2 |Tablelock |GRANT|S |NULL |SYSTABLES | 248 |ROW |S |2 |(1,17) |GRANT|S |NULL |SYSSCHEMAS | 202 |TABLE |IX |2 |Tablelock |GRANT|T |NULL |BATCHES | 202 |ROW |S |2 |(4,31) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,30) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(1,30) |GRANT|S |NULL |SYSTABLES | 202 |ROW |S |2 |(4,29) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(1,29) |GRANT|S |NULL |SYSTABLES | 202 |ROW |S |4 |(5,17) |GRANT|S |NULL |SYSCONGLOMERATES | 202 |ROW |S |4 |(1,7) |GRANT|S |NULL |SYSKEYS | 202 |ROW |S |2 |(4,28) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,27) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,26) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,25) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |2 |(4,24) |GRANT|S |NULL |SYSCOLUMNS | 202 |TABLE |IS |4 |Tablelock |GRANT|S |NULL |SYSCONSTRAINTS | 202 |ROW |S |4 |(4,23) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |4 |(1,28) |GRANT|S |NULL |SYSTABLES | 202 |ROW |S |4 |(5,16) |GRANT|S |NULL |SYSCONGLOMERATES | 202 |ROW |S |4 |(4,22) |GRANT|S |NULL |SYSCOLUMNS | 202 |ROW |S |4 |(1,27) |GRANT|S |NULL |SYSTABLES | 248 |ROW |S |2 |(1,27) |GRANT|S |NULL |SYSTABLES | 202 |TABLE |IX |1 |Tablelock |GRANT|T |NULL |REFERSTO | ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------- at org.apache.derby.iapi.error.StandardException.newException(Unknown Sour= ce) at org.apache.derby.impl.services.locks.Timeout.createException(Unknown So= urce) at org.apache.derby.impl.services.locks.Timeout.buildException(Unknown Sou= rce) at org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(Unkno= wn Source) at org.apache.derby.impl.services.locks.AbstractPool.lockObject(Unknown So= urce) at org.apache.derby.impl.services.locks.ConcurrentPool.lockObject(Unknown Source) at org.apache.derby.impl.store.raw.xact.RowLocking3.lockRecordForRead(Unkn= own Source) at org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknown Source) at org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknown Source) at org.apache.derby.impl.store.access.btree.index.B2IRowLocking3.lockRowOn= Page(Unknown Source) at org.apache.derby.impl.store.access.btree.index.B2IRowLocking3._lockScan= Row(Unknown Source) at org.apache.derby.impl.store.access.btree.index.B2IRowLockingRR.lockScan= Row(Unknown Source) at org.apache.derby.impl.store.access.btree.BTreeForwardScan.fetchRows(Unk= nown Source) at org.apache.derby.impl.store.access.btree.BTreeScan.fetchNext(Unknown So= urce) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaIn= dexMinion(Unknown Source) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaIn= dex(Unknown Source) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getColumnDescripto= rsScan(Unknown Source) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getColumnDescripto= rsScan(Unknown Source) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.finishTableDescrip= tor(Unknown Source) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getTableDescriptor= Index1Scan(Unknown Source) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getTableDescriptor= (Unknown Source)CREATE TABLE Expressions (exp VARCHAR(100),RID INT NOT NULL GENERATED ALWAYS AS IDENTITY , PRIMARY KEY(exp)); CREATE TABLE Batches (lastPub DATE,RID INT NOT NULL GENERATED ALWAYS AS IDENTITY , PRIMARY KEY(RID)); CREATE TABLE Objects (batch INT REFERENCES Batches(RID),star SMALLINT DEFAULT 0 NOT NULL,hide SMALLINT DEFAULT 0 NOT NULL,RID INT NOT NULL GENERATED ALWAYS AS IDENTITY , PRIMARY KEY(RID)); INSERT INTO Expressions(exp) VALUES('...so geht die Legende ...'); INSERT INTO Expressions(exp) VALUES('...legend has it ...'); SELECT RID FROM Batches WHERE RID >=3D (SELECT MAX(RID) FROM Batches); SELECT RefersTo.objectId FROM RefersTo WHERE RefersTo.exp=3D'...legend has it ...'; INSERT INTO Objects(batch) VALUES(-9); INSERT INTO Batches(lastPub) VALUES(null); INSERT INTO Expressions(exp) VALUES('...so geht die Legende ...'); INSERT INTO Expressions(exp) VALUES('...legend has it ...'); SELECT RID FROM Batches WHERE RID >=3D (SELECT MAX(RID) FROM Batches); SELECT RefersTo.objectId FROM RefersTo WHERE RefersTo.exp=3D'...legend has it ...'; INSERT INTO Objects(batch) VALUES(1); SELECT RID FROM Objects WHERE RID >=3D (SELECT MAX(RID) FROM Objects); INSERT INTO RefersTo(exp,objectId) VALUES('...legend has it ...',2); at org.apache.derby.impl.sql.compile.QueryTreeNode.getTableDescriptor(Unkn= own Source) at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unk= nown Source) at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknow= n Source) at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown Source) at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown Source) at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source) at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Sour= ce) at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepare= InternalStatement(Unknown Source) ... 48 more I had written this in an email, but it seems to have got lost. The details are different from the doc, and I'm not sure on how to unlock the refTable. 2010/3/1, Gabriele Kahlout : > hmm..I thought of it as the closest implementation to the doc. How > else would you write it? > > Indeed, the derby.properties file is almost empty. > > 2010/3/1, Rick Hillegas : >> Hi Gabriele, >> >> Have you checked the contents of derby.properties? The following code >> looks a little odd. It looks as though you are writing the Properties >> object to disk before you have actually set the properties. >> >> Regards, >> -Rick >> >> Gabriele Kahlout wrote: >>> I've added the following code to trace: >>> >>> final Properties prop =3D new java.util.Properties(); >>> prop.save(new FileOutputStream(new File("derby.properties")), >>> "derby.properties"); >>> prop.setProperty("derby.locks.deadlockTrace", "true"); >>> prop.setProperty("derby.language.logStatementText", "true"); >>> prop.setProperty("derby.language.logStatementText", "true"); >>> >>> Is this not enough? >>> >>> I still get the same stuff written to the log: >>> >>> 2010-03-01 19:14:59.864 GMT: >>> Booting Derby version The Apache Software Foundation - Apache Derby - >>> 10.5.1.1 - (764942): instance a816c00e-0127-1b27-19bf-00000065a670 >>> on database directory /Users/simpatico/ws/MemorizEasy/db.sqlwrapper >>> >>> Database Class Loader started - derby.database.classpath=3D'' >>> >>> 2010/3/1, Rick Hillegas : >>> >>>> Hi Gabriele, >>>> >>>> The file in question is derby.log, which I believe you have located. I= f >>>> you are not seeing the information you need there, it may be because >>>> you >>>> have not set the correct tracepoints in order to force the logging of >>>> that information. You may get a broader response to your question if >>>> you >>>> start a new thread under the title "Debugging lock timeouts". >>>> >>>> Hope this helps, >>>> -Rick >>>> >>>> Gabriele Kahlout wrote: >>>> >>>>> So can someone explain me (or refer to where it is explained) how to >>>>> read/find this log file with the wanted info? >>>>> >>>>> 2010/2/26, Rick Hillegas : >>>>> >>>>> >>>>>> Hi Gabriele, >>>>>> >>>>>> The following link will help you debug your lock contention problem: >>>>>> http://db.apache.org/derby/faq.html#debug_lock_timeout >>>>>> >>>>>> Hope this is useful, >>>>>> -Rick >>>>>> >>>>>> Gabriele Kahlout wrote: >>>>>> >>>>>> >>>>>>> Because of the trigger + procedure the update statement on the >>>>>>> affected table throws (not otherwise): >>>>>>> >>>>>>> java.sql.SQLException: The exception 'java.sql.SQLException: A lock >>>>>>> could not be obtained within the time requested' was thrown while >>>>>>> evaluating an expression. >>>>>>> >>>>>>> I tried to make it sleep for 250 nanos. But no avail. >>>>>>> >>>>>>> 2010/2/26, Rick Hillegas : >>>>>>> >>>>>>> >>>>>>> >>>>>>>> Hi Gabriele, >>>>>>>> >>>>>>>> Some comments inline... >>>>>>>> >>>>>>>> Gabriele Kahlout wrote: >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>>> Perhaps last question: how to pass as parameter also the table? >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> Here's a revised version of the procedure which takes the table an= d >>>>>>>> column names as parameters: >>>>>>>> >>>>>>>> public static void vetRow( String tableName, String columnName= , >>>>>>>> int >>>>>>>> columnValue ) throws SQLException >>>>>>>> { >>>>>>>> Connection conn =3D DriverManager.getConnection( >>>>>>>> "jdbc:default:connection" ); >>>>>>>> String query =3D "select count(*) from " + tableName +" wh= ere >>>>>>>> " >>>>>>>> + >>>>>>>> columnName + " =3D ?"; >>>>>>>> System.out.println( query ); >>>>>>>> PreparedStatement ps =3D conn.prepareStatement( query ); >>>>>>>> ps.setInt( 1, columnValue ); >>>>>>>> ResultSet rs =3D ps.executeQuery(); >>>>>>>> rs.next(); >>>>>>>> int result =3D rs.getInt( 1 ); >>>>>>>> >>>>>>>> rs.close(); >>>>>>>> ps.close(); >>>>>>>> >>>>>>>> if ( result >=3D 4 ) { throw new SQLException( "Too many >>>>>>>> copies >>>>>>>> of >>>>>>>> some value." ); } >>>>>>>> } >>>>>>>> } >>>>>>>> >>>>>>>> and the corresponding script to exercise this procedure: >>>>>>>> >>>>>>>> connect 'jdbc:derby:memory:dummy;create=3Dtrue'; >>>>>>>> >>>>>>>> create table t( a int ); >>>>>>>> >>>>>>>> create procedure vetRow( in tableName varchar( 128 ), in columnNam= e >>>>>>>> varchar( 128 ), in arg int ) >>>>>>>> language java parameter style java reads sql data >>>>>>>> external name 'z.vetRow'; >>>>>>>> >>>>>>>> create trigger t_trig >>>>>>>> no cascade before insert on t >>>>>>>> referencing new as newRow >>>>>>>> for each row call vetRow( 'T', 'A', newRow.a ); >>>>>>>> >>>>>>>> insert into t( a ) values ( 1 ); >>>>>>>> insert into t( a ) values ( 1 ); >>>>>>>> insert into t( a ) values ( 1 ); >>>>>>>> insert into t( a ) values ( 1 ); >>>>>>>> >>>>>>>> -- fails >>>>>>>> insert into t( a ) values ( 1 ); >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>>> Also, where can I look to understand why int a would be translate= d >>>>>>>>> as >>>>>>>>> the column a? >>>>>>>>> This doesn't help: >>>>>>>>> http://developers.sun.com/docs/javadb/10.5.3.0/ref/index.html >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> You will want to read the syntax descriptions for the CREATE >>>>>>>> PROCEDURE >>>>>>>> and CREATE TRIGGER commands in that Reference Guide. >>>>>>>> >>>>>>>> Hope this helps, >>>>>>>> -Rick >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>>> 2010/2/26, Rick Hillegas : >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>>> Hi Gabriele, >>>>>>>>>> >>>>>>>>>> Here is a vetting procedure which the trigger could call: >>>>>>>>>> >>>>>>>>>> public static void vetRow( int a ) throws SQLException >>>>>>>>>> { >>>>>>>>>> Connection conn =3D DriverManager.getConnection( >>>>>>>>>> "jdbc:default:connection" ); >>>>>>>>>> PreparedStatement ps =3D conn.prepareStatement( "select >>>>>>>>>> count(*) >>>>>>>>>> from t where a =3D ?" ); >>>>>>>>>> ps.setInt( 1, a ); >>>>>>>>>> ResultSet rs =3D ps.executeQuery(); >>>>>>>>>> rs.next(); >>>>>>>>>> int result =3D rs.getInt( 1 ); >>>>>>>>>> >>>>>>>>>> rs.close(); >>>>>>>>>> ps.close(); >>>>>>>>>> >>>>>>>>>> if ( result >=3D 4 ) { throw new SQLException( "Too many >>>>>>>>>> copies >>>>>>>>>> of >>>>>>>>>> value " + a + " in t." ); } >>>>>>>>>> } >>>>>>>>>> >>>>>>>>>> And here again is the sql script which exercises the trigger. I >>>>>>>>>> had >>>>>>>>>> to >>>>>>>>>> modify the trigger declaration slightly: instead of saying that >>>>>>>>>> it >>>>>>>>>> "contains sql" this version says "reads sql data": >>>>>>>>>> >>>>>>>>>> connect 'jdbc:derby:memory:dummy;create=3Dtrue'; >>>>>>>>>> >>>>>>>>>> create table t( a int ); >>>>>>>>>> >>>>>>>>>> create procedure vetRow( in arg int ) >>>>>>>>>> language java parameter style java reads sql data >>>>>>>>>> external name 'z.vetRow'; >>>>>>>>>> >>>>>>>>>> create trigger t_trig >>>>>>>>>> no cascade before insert on t >>>>>>>>>> referencing new as newRow >>>>>>>>>> for each row call vetRow( newRow.a ); >>>>>>>>>> >>>>>>>>>> insert into t( a ) values ( 1 ); >>>>>>>>>> insert into t( a ) values ( 1 ); >>>>>>>>>> insert into t( a ) values ( 1 ); >>>>>>>>>> insert into t( a ) values ( 1 ); >>>>>>>>>> >>>>>>>>>> -- fails >>>>>>>>>> insert into t( a ) values ( 1 ); >>>>>>>>>> >>>>>>>>>> Depending on your usage pattern, it might make more sense to >>>>>>>>>> declare >>>>>>>>>> an >>>>>>>>>> AFTER trigger which invokes a vetting procedure once per >>>>>>>>>> INSERT/UPDATE >>>>>>>>>> rather than per row. >>>>>>>>>> >>>>>>>>>> Hope this helps, >>>>>>>>>> -Rick >>>>>>>>>> >>>>>>>>>> Gabriele Kahlout wrote: >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>>> The procedure would run the sanity check query and raise an >>>>>>>>>>> exception >>>>>>>>>>> if the sanity check failed. >>>>>>>>>>> Can you show me an example of how this exception could be >>>>>>>>>>> thrown? >>>>>>>>>>> >>>>>>>>>>> About the user-coded function, how may I code what I asked for? >>>>>>>>>>> A >>>>>>>>>>> loop >>>>>>>>>>> that counts for each distinct value it's occurences, and return= s >>>>>>>>>>> true/false if some count is greater than 5. >>>>>>>>>>> >>>>>>>>>>> 2010/2/26, Rick Hillegas : >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>>> Hi Gabriele, >>>>>>>>>>>> >>>>>>>>>>>> Here's an example of how Derby triggers invoke database >>>>>>>>>>>> procedures. >>>>>>>>>>>> If >>>>>>>>>>>> this is still not clear, please keep asking questions. We will >>>>>>>>>>>> get >>>>>>>>>>>> to >>>>>>>>>>>> the bottom of this: >>>>>>>>>>>> >>>>>>>>>>>> connect 'jdbc:derby:memory:dummy;create=3Dtrue'; >>>>>>>>>>>> >>>>>>>>>>>> create table t( a int ); >>>>>>>>>>>> >>>>>>>>>>>> create procedure vetRow( in arg int ) >>>>>>>>>>>> language java parameter style java contains sql >>>>>>>>>>>> external name 'z.vetRow'; >>>>>>>>>>>> >>>>>>>>>>>> create trigger t_trig >>>>>>>>>>>> no cascade before insert on t >>>>>>>>>>>> referencing new as newRow >>>>>>>>>>>> for each row call vetRow( newRow.a ); >>>>>>>>>>>> >>>>>>>>>>>> insert into t( a ) values ( 1 ); >>>>>>>>>>>> >>>>>>>>>>>> Another comment inline... >>>>>>>>>>>> >>>>>>>>>>>> Gabriele Kahlout wrote: >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>>> That's what i do in SQLite. The question is how to do it in >>>>>>>>>>>>> java >>>>>>>>>>>>> DB >>>>>>>>>>>>> sql >>>>>>>>>>>>> syntax. >>>>>>>>>>>>> In sqlite it is SELECT( RAISE, 'error) WHERE ... >>>>>>>>>>>>> in java db?? >>>>>>>>>>>>> >>>>>>>>>>>>> Also, I was wondering if I could find a way to implement the >>>>>>>>>>>>> constraint as a column constraint. Unfortunately the details = , >>>>>>>>>>>>> are >>>>>>>>>>>>> far >>>>>>>>>>>>> from complete. >>>>>>>>>>>>> http://developers.sun.com/docs/javadb/10.5.3.0/ref/index.html >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>> I don't think so. The only kind of constraint you could use >>>>>>>>>>>> here >>>>>>>>>>>> would >>>>>>>>>>>> be a CHECK constraint which invokes a user-coded function. The >>>>>>>>>>>> function >>>>>>>>>>>> would not be allowed to issue SQL. >>>>>>>>>>>> >>>>>>>>>>>> Hope this helps, >>>>>>>>>>>> -Rick >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>>> Basically I'm trying to check that no more than 4 rows have >>>>>>>>>>>>> the >>>>>>>>>>>>> same >>>>>>>>>>>>> value for the column. In SQL that would be the count -group b= y >>>>>>>>>>>>> query, >>>>>>>>>>>>> as mentioned below, but since that counts as a not allowed >>>>>>>>>>>>> subquery, >>>>>>>>>>>>> is there some other way in java db to achieve exactly that? >>>>>>>>>>>>> >>>>>>>>>>>>> 2010/2/26, Rick Hillegas : >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>>> Hi Gabriele, >>>>>>>>>>>>>> >>>>>>>>>>>>>> One way to solve this problem would be to have your trigger >>>>>>>>>>>>>> call >>>>>>>>>>>>>> a >>>>>>>>>>>>>> database procedure, passing in whatever parameters you need >>>>>>>>>>>>>> from >>>>>>>>>>>>>> the >>>>>>>>>>>>>> triggering row. The procedure would run the sanity check >>>>>>>>>>>>>> query >>>>>>>>>>>>>> and >>>>>>>>>>>>>> raise >>>>>>>>>>>>>> an exception if the sanity check failed. >>>>>>>>>>>>>> >>>>>>>>>>>>>> Hope this helps, >>>>>>>>>>>>>> -Rick >>>>>>>>>>>>>> >>>>>>>>>>>>>> Gabriele Kahlout wrote: >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>>> Hello, >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> I'm porting my application from SQLite to Java DB, but don'= t >>>>>>>>>>>>>>> know >>>>>>>>>>>>>>> how >>>>>>>>>>>>>>> to enforce an assertion, achieved in SQLite by: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> st.execute("CREATE TRIGGER _fk_meanings_update_1 BEFORE >>>>>>>>>>>>>>> UPDATE >>>>>>>>>>>>>>> ON >>>>>>>>>>>>>>> " >>>>>>>>>>>>>>> + >>>>>>>>>>>>>>> batTable.getName() + " FOR EACH ROW BEGIN SELECT >>>>>>>>>>>>>>> RAISE(FAIL, >>>>>>>>>>>>>>> 'error') >>>>>>>>>>>>>>> WHERE (EXISTS (SELECT COUNT(*) FROM " + batTable.getName() = + >>>>>>>>>>>>>>> " >>>>>>>>>>>>>>> GROUP >>>>>>>>>>>>>>> BY " + lastPubColumn.getName() + " HAVING >>>>>>>>>>>>>>> COUNT(*)>"+maxPub+")); >>>>>>>>>>>>>>> END;"); >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> However the constraint couldn't be added as a table >>>>>>>>>>>>>>> constraint, >>>>>>>>>>>>>>> and >>>>>>>>>>>>>>> I'm not finding how to raise an exception with Java DB, >>>>>>>>>>>>>>> although >>>>>>>>>>>>>>> it's >>>>>>>>>>>>>>> described here: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> http://developers.sun.com/docs/javadb/10.5.3.0/devguide/ind= ex.html >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> One way was to set the identity column to null, but that wa= s >>>>>>>>>>>>>>> not >>>>>>>>>>>>>>> allowed at compile time too. I guess I'd have to use some >>>>>>>>>>>>>>> dirty >>>>>>>>>>>>>>> way. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>> >>>>> >>>>> >>>> >>> >>> >>> >> >> > > > -- > Regards, > K. Gabriele > > --- unchanged since 25/1/10 --- > P.S. Unless a notification (LON), please reply either with an answer > OR with " ACK" appended to this subject within 48 hours. Otherwise, I > might resend. > In(LON, this) =E2=88=A8 In(48h, TimeNow) =E2=88=A8 =E2=88=83x. In(x, MyIn= box) =E2=88=A7 IsAnswerTo(x, > this) =E2=88=A8 (In(subject(this), subject(x)) =E2=88=A7 In(ACK, subject(= x)) =E2=88=A7 > =C2=ACIsAnswerTo(x,this)) =E2=87=92 =C2=ACIResend(this). > > Also note that correspondence may be received only from specified a > priori senders, or if the subject of this email ends with a code, eg. > -LICHT01X, then also from senders whose reply contains it. > =E2=88=80x. In(x, MyInbox) =E2=87=92 In(senderAddress(x), MySafeSenderLis= t) =E2=88=A8 (=E2=88=83y. > In(y, subject(this) ) =E2=88=A7 In(y,x) =E2=88=A7 isCodeLike(y, -LICHT01X= ) ). > --=20 Regards, K. Gabriele --- unchanged since 25/1/10 --- P.S. Unless a notification (LON), please reply either with an answer OR with " ACK" appended to this subject within 48 hours. Otherwise, I might resend. In(LON, this) =E2=88=A8 In(48h, TimeNow) =E2=88=A8 =E2=88=83x. In(x, MyInbo= x) =E2=88=A7 IsAnswerTo(x, this) =E2=88=A8 (In(subject(this), subject(x)) =E2=88=A7 In(ACK, subject(x)= ) =E2=88=A7 =C2=ACIsAnswerTo(x,this)) =E2=87=92 =C2=ACIResend(this). Also note that correspondence may be received only from specified a priori senders, or if the subject of this email ends with a code, eg. -LICHT01X, then also from senders whose reply contains it. =E2=88=80x. In(x, MyInbox) =E2=87=92 In(senderAddress(x), MySafeSenderList)= =E2=88=A8 (=E2=88=83y. In(y, subject(this) ) =E2=88=A7 In(y,x) =E2=88=A7 isCodeLike(y, -LICHT01X) = ).