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:32:34 GMT
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) ).

Mime
View raw message