db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Debugging lock timeouts - versuchen01X
Date Mon, 01 Mar 2010 20:38:24 GMT
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.
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>                           
>>>>>           
>>>
>>>       
>>     
>
>
>   


Mime
View raw message