Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (nike.apache.org: local policy)
MIME-Version: 1.0
Date: Mon, 1 Mar 2010 20:16:45 +0100
Message-ID: <1f5d398f1003011116n663ac9e1i151c26601215cf83@mail.gmail.com>
Subject: Debugging lock timeouts - versuchen01X
From: Gabriele Kahlout <gabriele@mysimpatico.com>
To: Derby Discussion <derby-user@db.apache.org>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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 <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, i=
nt
>>>>> columnValue ) throws SQLException
>>>>>     {
>>>>>         Connection conn =3D DriverManager.getConnection(
>>>>> "jdbc:default:connection" );
>>>>>         String query =3D "select count(*) from " + tableName +" where=
 " +
>>>>> 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 copi=
es
>>>>> 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 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 a=
s
>>>>>> 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 PROCEDUR=
E
>>>>> 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 =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 co=
pies
>>>>>>> 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 declar=
e
>>>>>>> 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 procedure=
s.
>>>>>>>>> If
>>>>>>>>> this is still not clear, please keep asking questions. We will ge=
t
>>>>>>>>> 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 , a=
re
>>>>>>>>>> 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 cal=
l
>>>>>>>>>>> a
>>>>>>>>>>> database procedure, passing in whatever parameters you need fro=
m
>>>>>>>>>>> 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, althou=
gh
>>>>>>>>>>>> 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 n=
ot
>>>>>>>>>>>> allowed at compile time too. I guess I'd have to use some dirt=
y
>>>>>>>>>>>> way.
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>
>>>>
>>>>
>>>
>>
>>
>>
>
>


--=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) =
).