Hi Kevin,
I can only see the number of SQl statements and the error message even
though i have turned on all levels of logging. I'll try to get some more
today also, but I did not see much information other than the sqls. Please
let me know if I need set any other properties ..
<prop> key="openjpa.Log">DefaultLevel=TRACE, Runtime=TRACE, Tool=TRACE,
SQL=TRACE</prop>
<prop key="show_sql">true</prop>
Thanks,
Chandra
Kevin Sutter wrote:
>
> Hi Chandra,
> Do you have the complete trace in a file that you could attach to this
> exchange? Or, post on filebin or something? Thanks!
>
> Kevin
>
> On Fri, Apr 24, 2009 at 3:34 AM, Chandra Sarath <[email protected]> wrote:
>
>>
>> Hi,
>>
>> We have turned on TRACE and we can only see update Sqls for the table
>> that
>> is locked.
>>
>>
>> <prop
>> key="openjpa.Log">DefaultLevel=TRACE, Runtime=TRACE, Tool=TRACE,
>> SQL=TRACE</prop>
>> <prop key="show_sql">true</prop>
>>
>> Here is how we tested.
>>
>> 1. We have locked a child table with shared lock (Read only table)
>> and
>> ran an update from parent table class.
>> entityManger.merge(parentClass).
>>
>> We saw an update sql in TRACE that is trying to update the Locked
>> table (say table emp). Since this table is locked, database timed out at
>> 30
>> sec (timeout property in datasource), but Jpa tried to execute update sql
>> again. It tried to update 2 times and timed out and showed the 40001 (Db2
>> -911) error. for stacktracesee below.
>>
>> 2. We tried to merge again one more time and it tried to update the
>> locked table for 4 times and timeout with the above error.
>>
>> 3. We tried to merge one more time, and this time it tried to update
>> the
>> locked table for more than 15 times and continued trying the same until
>> we
>> disconnected the database manually.
>>
>> So this is a inconsistent behavior of OpenJPA and is it a bug? We wonder
>> why
>> it is trying unlimited times to update a locked (read only) table. But
>> for
>> inserts it times out properly and shows the proper error. I do not knwo
>> why
>> it is behaving differently for updates.
>>
>> Please advice any Jpa timeout parameters that I can use for the readonly
>> locks.
>>
>> It is a top priority issue now. Any help on this is greatly appreciated.
>> Please find the stacktrace below..
>>
>> [4/23/09 13:35:27:501 PDT] 00000040 SystemErr Caused by:
>> org.apache.openjpa.lib.jdbc.ReportingSQLException: The
>> current transaction has been rolled back because of a deadlock or
>> timeout.
>> Reason code "68". {prepstmnt 1684038752
>> UPDATE SCHEMA.TABLE SET REC_UPD_TS = ?, AVERAGE_MONTH_INCOME = ?,
>> TEST_CODE
>> = ?,TEST_PERIOD_CODE = ? WHERE ID = ?
>> [params=(Timestamp) 2009-04-23 13:25:25.365, (long) 888, (String) H,
>> (String) W, (long) 71383]} [co
>> de=-911, state=40001]
>> at
>>
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:192)
>> at
>>
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$700(LoggingConnectionDecorator.java:57)
>> at
>>
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeU
>> pdate(LoggingConnectionDecorator.java:866)
>> at
>>
>> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269
>> )
>> at
>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.jav
>> a:1504)
>> at
>>
>> org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.executeUpdate(PreparedStatementManagerImpl.jav
>> a:151)
>> at
>>
>> org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.ja
>> va:120)
>> at
>>
>> org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.batchOrExecuteRow(BatchingPreparedStat
>> ementManagerImpl.java:102)
>> at
>>
>> org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushAndUpdate(BatchingPreparedStateme
>> ntManagerImpl.java:86)
>> at
>>
>> org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.jav
>>
>>
>>
>> Thanks,
>> Chandra
>>
>>
>>
>>
>> Chandra Sarath wrote:
>> >
>> > Hi,
>> >
>> > We use OpenJpa, DB2 and Websphere 6.1. We have created a Datasource in
>> > Websphere console that connects DB2 using a non-xa driver.
>> >
>> > We were testing our application about how it rolls back the data when
>> > there is a shared lock in a table. we have following scenarios. We have
>> > also set a Timeout of 30 seconds in Datasource/connection properties.
>> >
>> > 1. Insert a record in a read only (Shared lock).
>> > 2. Update a record in read only table (Shared lock).
>> >
>> > For Scenario 1, after 30 seconds application times out and gives us
>> > proper error. But for scenario 2, it goes in infinite
>> > loop and tries to update Database for every 30 secs, and it never
>> stops
>> > trying to.
>> >
>> > Is there any extra property that we need to set in Jpa level or
>> > Datasource level so application can timeout after 30 seconds?
>> >
>> > we use entityManager.persist() for inserts and entityManager.merge()
>> for
>> > updates. Can someone help us on this?
>> >
>> > Thank you!
>> > Chandra
>> >
>>
>> --
>> View this message in context:
>> http://n2.nabble.com/OpenJpa%2C-and-DB2-shared-lock-tp2675084p2691242.html
>> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>>
>>
>
>
--
View this message in context:
http://n2.nabble.com/OpenJpa%2C-and-DB2-shared-lock-tp2675084p2693349.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
