db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gabriele Kahlout <gabri...@mysimpatico.com>
Subject Re: Debugging lock timeouts - versuchen01X
Date Mon, 01 Mar 2010 21:34:52 GMT
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(Unknown
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(Unknown
Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
	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:426)
	at com.mysimpatico.memorizeasy.engine.Database.insertWords(Database.java:424)
	at com.mysimpatico.memorizeasy.engine.Database.insertWords(Database.java:461)
	at com.mysimpatico.memorizeasy.engine.Database.insertWords(Database.java:290)
	at com.mysimpatico.memorizeasy.engine.Database.importWords(Database.java:296)
	at com.mysimpatico.memorizeasy.test.DatabaseTest.testImportWords(DatabaseTest.java:118)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99)
	at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81)
	at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
	at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)
	at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)
	at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:66)
	at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)
	at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)
	at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
	at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:45)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.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.wrapArgsForTransportAcrossDRDA(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.wrapArgsForTransportAcrossDRDA(Unknown
Source)
	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
Source)
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
	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.java:171)
	at org.apache.derby.exe.ac4ac48095x0127x1b97xa6d9x000000652bb04.g0(Unknown
Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(Unknown Source)
	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(Unknown
Source)
	at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown
Source)
	at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown
Source)
	at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unknown
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 Source)
	... 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 Source)
	at org.apache.derby.impl.services.locks.Timeout.createException(Unknown Source)
	at org.apache.derby.impl.services.locks.Timeout.buildException(Unknown Source)
	at org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(Unknown
Source)
	at org.apache.derby.impl.services.locks.AbstractPool.lockObject(Unknown Source)
	at org.apache.derby.impl.services.locks.ConcurrentPool.lockObject(Unknown
Source)
	at org.apache.derby.impl.store.raw.xact.RowLocking3.lockRecordForRead(Unknown
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.lockRowOnPage(Unknown
Source)
	at org.apache.derby.impl.store.access.btree.index.B2IRowLocking3._lockScanRow(Unknown
Source)
	at org.apache.derby.impl.store.access.btree.index.B2IRowLockingRR.lockScanRow(Unknown
Source)
	at org.apache.derby.impl.store.access.btree.BTreeForwardScan.fetchRows(Unknown
Source)
	at org.apache.derby.impl.store.access.btree.BTreeScan.fetchNext(Unknown Source)
	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaIndexMinion(Unknown
Source)
	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaIndex(Unknown
Source)
	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getColumnDescriptorsScan(Unknown
Source)
	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getColumnDescriptorsScan(Unknown
Source)
	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.finishTableDescriptor(Unknown
Source)
	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getTableDescriptorIndex1Scan(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 >= (SELECT MAX(RID) FROM Batches);
SELECT RefersTo.objectId FROM RefersTo WHERE RefersTo.exp='...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 >= (SELECT MAX(RID) FROM Batches);
SELECT RefersTo.objectId FROM RefersTo WHERE RefersTo.exp='...legend
has it ...';
INSERT  INTO Objects(batch) VALUES(1);
SELECT RID FROM Objects WHERE RID >= (SELECT MAX(RID) FROM Objects);
INSERT  INTO RefersTo(exp,objectId) VALUES('...legend has it ...',2);

	at org.apache.derby.impl.sql.compile.QueryTreeNode.getTableDescriptor(Unknown
Source)
	at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown
Source)
	at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown
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 Source)
	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.prepareInternalStatement(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 <gabriele@mysimpatico.com>:
> 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 <Richard.Hillegas@sun.com>:
>> 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 = 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=''
>>>
>>> 2010/3/1, Rick Hillegas <Richard.Hillegas@sun.com>:
>>>
>>>> Hi Gabriele,
>>>>
>>>> The file in question is derby.log, which I believe you have located. If
>>>> 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 <Richard.Hillegas@sun.com>:
>>>>>
>>>>>
>>>>>> 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 <Richard.Hillegas@sun.com>:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>> 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 and
>>>>>>>> column names as parameters:
>>>>>>>>
>>>>>>>>     public static void vetRow( String tableName, String columnName,
>>>>>>>> int
>>>>>>>> columnValue ) throws SQLException
>>>>>>>>     {
>>>>>>>>         Connection conn = DriverManager.getConnection(
>>>>>>>> "jdbc:default:connection" );
>>>>>>>>         String query = "select count(*) from " + tableName +" where
>>>>>>>> "
>>>>>>>> +
>>>>>>>> columnName + " = ?";
>>>>>>>>         System.out.println( query );
>>>>>>>>         PreparedStatement ps = conn.prepareStatement( query );
>>>>>>>>         ps.setInt( 1, columnValue );
>>>>>>>>         ResultSet rs = ps.executeQuery();
>>>>>>>>         rs.next();
>>>>>>>>         int result = rs.getInt( 1 );
>>>>>>>>
>>>>>>>>         rs.close();
>>>>>>>>         ps.close();
>>>>>>>>
>>>>>>>>         if ( result >= 4 ) { throw new SQLException( "Too many
>>>>>>>> copies
>>>>>>>> of
>>>>>>>> some value." ); }
>>>>>>>>     }
>>>>>>>> }
>>>>>>>>
>>>>>>>> and the corresponding script to exercise this procedure:
>>>>>>>>
>>>>>>>> connect 'jdbc:derby:memory:dummy;create=true';
>>>>>>>>
>>>>>>>> create table t( a int );
>>>>>>>>
>>>>>>>> create procedure vetRow( in tableName varchar( 128 ), in columnName
>>>>>>>> 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 translated
>>>>>>>>> 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 <Richard.Hillegas@sun.com>:
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>> Hi Gabriele,
>>>>>>>>>>
>>>>>>>>>> Here is a vetting procedure which the trigger could call:
>>>>>>>>>>
>>>>>>>>>>     public static void vetRow( int a ) throws SQLException
>>>>>>>>>>     {
>>>>>>>>>>         Connection conn = DriverManager.getConnection(
>>>>>>>>>> "jdbc:default:connection" );
>>>>>>>>>>         PreparedStatement ps = conn.prepareStatement( "select
>>>>>>>>>> count(*)
>>>>>>>>>> from t where a = ?" );
>>>>>>>>>>         ps.setInt( 1, a );
>>>>>>>>>>         ResultSet rs = ps.executeQuery();
>>>>>>>>>>         rs.next();
>>>>>>>>>>         int result = rs.getInt( 1 );
>>>>>>>>>>
>>>>>>>>>>         rs.close();
>>>>>>>>>>         ps.close();
>>>>>>>>>>
>>>>>>>>>>         if ( result >= 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=true';
>>>>>>>>>>
>>>>>>>>>> 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 returns
>>>>>>>>>>> true/false if some count is greater than 5.
>>>>>>>>>>>
>>>>>>>>>>> 2010/2/26, Rick Hillegas <Richard.Hillegas@sun.com>:
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>> 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=true';
>>>>>>>>>>>>
>>>>>>>>>>>> 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 by
>>>>>>>>>>>>> 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 <Richard.Hillegas@sun.com>:
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>> 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/index.html
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> One way was to set the identity column to null, but that was
>>>>>>>>>>>>>>> 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) ∨ In(48h, TimeNow) ∨ ∃x. In(x, MyInbox) ∧ IsAnswerTo(x,
> this) ∨ (In(subject(this), subject(x)) ∧ In(ACK, subject(x)) ∧
> ¬IsAnswerTo(x,this)) ⇒ ¬IResend(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.
> ∀x. In(x, MyInbox) ⇒ In(senderAddress(x), MySafeSenderList) ∨ (∃y.
> In(y, subject(this) ) ∧ In(y,x) ∧ isCodeLike(y, -LICHT01X) ).
>


-- 
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) ∨ In(48h, TimeNow) ∨ ∃x. In(x, MyInbox) ∧ IsAnswerTo(x,
this) ∨ (In(subject(this), subject(x)) ∧ In(ACK, subject(x)) ∧
¬IsAnswerTo(x,this)) ⇒ ¬IResend(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.
∀x. In(x, MyInbox) ⇒ In(senderAddress(x), MySafeSenderList) ∨ (∃y.
In(y, subject(this) ) ∧ In(y,x) ∧ isCodeLike(y, -LICHT01X) ).

Mime
View raw message