Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2013
    Posts
    21

    Unanswered: Select For Update and timeout

    Hi all,

    My configuration:
    - WebSphere 8.0.0.5
    - DB2 10.1 (Linux)
    - My JEE application.
    In my application I use two datasources:
    - A datasource with transaction (for Hibernate).
    - A datasource without transaction (for native SQL, by JDBC).

    My JEE application execute the query SELECT FOR UPDATE, and just after, when my application execute query UPDATE on the same table, my application is locked/frozen on the call of executeQuery() fonction.

    If I replace WebSpere by Tomcat, there is no problem !

    Here is my callstack:
    Code:
    [6/13/13 9:41:50:464 GMT+01:00] 00000005 TimeoutManage I   WTRN0006W: Transaction 0000013F3CAE1A9D0000000172B10C70DFF4792F27FBCAF791E3AFEB03B6E271C464A9740000013F3CAE1A9D0000000172B10C70DFF4792F27FBCAF791E3A
    FEB03B6E271C464A97400000001 has timed out after 300 seconds.
    [6/13/13 9:41:50:465 GMT+01:00] 00000005 TimeoutManage I   WTRN0124I: When the timeout occurred the thread with which the transaction is, or was most recently, associated was Thread[WebContainer : 1,5,main].
     The stack trace of this thread when the timeout occurred was:
            java.net.SocketInputStream.socketRead0(Native Method)
            java.net.SocketInputStream.read(SocketInputStream.java:140)
            com.ibm.db2.jcc.t4.cb.b(cb.java:198)
            com.ibm.db2.jcc.t4.cb.c(cb.java:249)
            com.ibm.db2.jcc.t4.cb.c(cb.java:360)
            com.ibm.db2.jcc.t4.cb.v(cb.java:1145)
            com.ibm.db2.jcc.t4.fb.a(fb.java:39)
            com.ibm.db2.jcc.t4.t.a(t.java:32)
            com.ibm.db2.jcc.t4.ub.i(ub.java:135)
            com.ibm.db2.jcc.am.wm.hb(wm.java:1949)
            com.ibm.db2.jcc.am.xm.pc(xm.java:2996)
            com.ibm.db2.jcc.am.xm.b(xm.java:3766)
            com.ibm.db2.jcc.am.xm.ec(xm.java:2241)
            com.ibm.db2.jcc.am.xm.execute(xm.java:2221)
            com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecute(WSJdbcPreparedStatement.java:956)
            com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.execute(WSJdbcPreparedStatement.java:623)
    [...]  My application call Query fonction here.    
            sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
            sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
            sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
            java.lang.reflect.Method.invoke(Method.java:611)
            org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
            org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
            org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
            org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:80)
            sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
            sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
            sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
            java.lang.reflect.Method.invoke(Method.java:611)
            org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
            org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
            org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:65)
            org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:161)
            org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
            org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
            org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
            org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
            org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
            $Proxy145.createAbstractNodeGen(Unknown Source)
            [...]
            sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
            sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
            sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
            java.lang.reflect.Method.invoke(Method.java:611)
            org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
            org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
            org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
            org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
            org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
            org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
            org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
            org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
            $Proxy131.createNode(Unknown Source)
            [...]
            sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
            sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
            sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
            java.lang.reflect.Method.invoke(Method.java:611)
            org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
            org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
            org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
            org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
            org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
            org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
            $Proxy727.execute(Unknown Source)
            [...]
            net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
            org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:689)
            org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
            org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
            org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
            org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622)
            [...]
    Thanks for your a attention.

    Nassa.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by nassarane View Post
    My JEE application execute the query SELECT FOR UPDATE, and just after, when my application execute query UPDATE on the same table, my application is locked/frozen on the call of executeQuery() fonction.
    This would happen if the two statements were executed from different JDBC connections. I suggest you reduce the lock timeout setting to something less than 300 seconds to verify that you indeed have a lock wait.

    Quote Originally Posted by nassarane View Post

    If I replace WebSpere by Tomcat, there is no problem !
    It might be an indication of the fact that the JDBC connection pooling is implemented or configured differently in these two containers.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Check the app server default Isolation Level. I believe that it is set to RS or RR by default for Websphere and it should be set to CS. Note that the isolation levels may be described differently in Websphere as follows:

    Code:
    Table 1. Equivalent JDBC and DB2 isolation levels JDBC value DB2 isolation level
    
    java.sql.Connection.TRANSACTION_SERIALIZABLE Repeatable read (RR) 
    java.sql.Connection.TRANSACTION_REPEATABLE_READ Read stability (RS)
    java.sql.Connection.TRANSACTION_READ_COMMITTED Cursor stability (CS) 
    java.sql.Connection.TRANSACTION_READ_UNCOMMITTED Uncommitted read (UR)
    IBM Data Server Driver for JDBC and SQLJ isolation levels - IBM DB2 9.7 for Linux, UNIX, and Windows
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Feb 2013
    Posts
    21
    Hi Marcus,

    Thanks for your response.

    I found how to configure the isolation level in WebSphere : IBM Transaction Isolation Levels and WebSphere Application Server - United States

    For the notx datasource (used by the native SQL request/update) I tryed all possible values 1, 2, 4, 8 : same problem.
    With the value 0, it's worse.

    I did not change the level for the tx datasource used by Hibernate (default value).

    I try to remove the "FOR UPDATE" on the "SELECT" : same problem !
    The problem is not on the "SELECT FOR UPDATE" but on the last SQL request : the "UPDATE".

    I do not know what to do...

    Nassa.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    What is the error message you get in db2? sql0911n rc=68? To execute UPDATE, db2 needs to convert the lock from U to X. Some other applic.ation must be holding a lock incompatible with X. Try capturing application and lock snapshots

  6. #6
    Join Date
    Feb 2013
    Posts
    21
    Hi DB2Girl,

    I'm not familiar with DB2.
    Sorry but I do not understand anything of what you wrote.

    Thanks.

    Nassa.

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    You need isolation level CS:
    2 TRANSACTION_READ_COMMITTED Cursor Stability (CS)


    Did you try restarting all apps after changing CS to 2?

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

  9. #9
    Join Date
    Feb 2013
    Posts
    21
    DB2Girl,

    Yes I tryed with isolation value 2, and after I restarted WebSphere, but it does not work.

    The only thing I have not tried is to light a candle or sacrificing a chicken .

    Have a good week-end.

    Nassa.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •