Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2008
    Posts
    9

    Unanswered: Hibernate-DB2: DB2 in lock

    Hello,
    I have an application that persist data into DBMS through Hibernate Framework.
    I used it on MySql, on Oracle and now on DB2.

    Only with DB2 something strange happens:
    - when i execute my application, HIBERNATE isn't able to complete persist data on DB2;
    - any error or exception is generated;
    - the application is in a "wait state" until the warning below is printed on log file:

    2008-02-14 16:43:37,519 WARN [org.jboss.tm.TransactionImpl] Transaction TransactionImpl:XidImpl[FormatId=257, GlobalId=oracolo-coll/24, BranchQual=, localId=24] timed out. status=STATUS_ACTIVE

    - DB2 is completely LOCKED! All the tables concerning the Hibernate process ara locked. If i run a simple query like "SELECT * FROM <a table name>", it goes in lock! Only if i stop and start DB2 from his control centre I recovery the functionality of my DB2.


    Since my log, it seems that Hibernate locks on a sequence of DB2 but I tried to insert data and it's fine! Finally the same tables and the same sequences on Oracle worked fine! So the problem is only DB2.

    Someone could help me?
    Thanx.

  2. #2
    Join Date
    Feb 2008
    Posts
    9
    I've found the error!

    Hibernate (in my application) uses a datasource to connect with database.
    Datasource is defined on my application server (jboss).

    If i set a datasource for local transaction (class com.ibm.db2.jcc.DB2Driver in db-ds.xml) everything goes well.
    If i set a datasource for distributed transaction (class com.ibm.db2.jcc.DB2XADataSource in db2-xa-ds.xml) every table goes in lock state !!!

    Someone could tell me how I can configure my datasource for distributed transaction? I need the transaction be DISTRIBUTED

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You may have some in-doubt transactions. Have a look at the LIST INDOUBT TRANSACTIONS command, which DB2 provides. This may resolve your DB2 being "locked" issue.

    In the end, you will have to figure out which of the transaction participants cannot do an xa_prepare or xa_commit and for what reason. Since you are using Hibernate as the TP monitor/coordinator, you have to investigate at this level, I'd say.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Feb 2008
    Posts
    9
    Thank you Stolze.

    The command LIST INDOUBT TRANSACTIONS doesn't show any in-doubt transactions but my 22 table are in lock state

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    So who/which program is holding those locks? And what type of locks are we talking about?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Feb 2008
    Posts
    9
    I used the command "db2 get snapshot for all" to see the lock status of entire DB.

    For all my table I see the follow informations:

    Lock Name = 0x04000E00000000000000000054
    Lock Attributes = 0x00000000
    Delivery Indicators = 0x40000000
    Number of lock = 2
    Number of lock mantained = 0
    Lock object name = 14
    Object Type = Table
    Tablespace Name = DATI
    Table Schema = ORACOLO
    Table Name = COUNTRY
    Type = IX

    or

    Lock Name = 0x04000301040000030000000052
    Lock Attributes = 0x00000008
    Indicators of Delivery = 0x40000000
    Number of lock = 2
    Number of lock mantained = 0
    Lock object name = 50331652
    Object type = Row
    Tablespace Name = DATI
    Table Schema = ORACOLO
    Table Name = POSTEPAY_SUBSCRIPTION
    Type = X

    (PS. i translated for you the parameters of the snapshot italian-->english)

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    In front of the "List of locks" you see which application is holding the locks in the list. So you have to figure out which application is holding the lock and then go from there.

    Also, you said "or" - what does that mean?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Feb 2008
    Posts
    9
    When I say "or" I mean that each table is in lock state with:

    "Object Type = Table
    ...
    Type = IX"


    AND

    "Object type = Row
    ...
    Type = X"



    Finally, before "List of locks" I have:

    Database Lock Snapshot

    Database name = TEST
    Database path = D:\DB2_01\NODE0000\SQL00002\
    Input database alias = TEST
    Locks held = 46
    Applications currently connected = 6
    Agents currently waiting on locks = 0
    Snapshot timestamp = 26/02/2008 20:10:00.134520

    Application handle = 176
    Application ID = *LOCAL.DB2_01.080226190128
    Sequence number = 00017
    Application name = javaw.exe
    CONNECT Authorization ID = FABIANA
    Application status = UOW in wait
    Status change time = 26/02/2008 20:06:26.515990
    Application code page = 1208
    Locks held = 0
    Total wait time (ms) = 0

    Application handle = 162
    Application ID = *LOCAL.DB2_01.080226185517
    Sequence number = 00002
    Application name = java.exe
    CONNECT Authorization ID = ILOG
    Application status = UOW Executing
    Status change time = 26/02/2008 19:58:12.967576
    Application code page = 1208
    Locks held = 22
    Total wait time (ms) = 0



    Thank you a lot STOLZE!

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    In case this is the complete output, have a look at application handle 162 (some sort of Java application that connects as user ILOG) and try to figure out why this application is not releasing its locks.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Feb 2008
    Posts
    9
    The applications:
    - "CONNECT Authorization ID = FABIANA"
    and
    - "CONNECT Authorization ID = ILOG"
    are in a unique Transaction and both uses Hibernate in the same way to persist data.

    Finally, when we integrated the system (so, the same code!) with Oracle DBMS there wasn't this kind of error.

    I think is a wrong setting of the driver JDBC on JBoss... is it possible?

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by eugen10

    I think is a wrong setting of the driver JDBC on JBoss... is it possible?
    At this point anything is possible. Oracle uses entirely different concurrency control mechanism to that in DB2, so I suspect the problem was still there when you were running Oracle but it was not so obvious.

    My feeling is that in your code you don't complete database (Hibernate) transactions and/or sessions properly. DB2 will hold locks acquired during a transaction until the transaction is either committed or rolled back.
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Feb 2008
    Posts
    9
    My colleagues thinks the problem is in the management of "Nested Transaction" by XA DB2 Driver.

    The application with "CONNECT Authorization ID = ILOG" is NESTED in the transaction opened by that with "CONNECT Authorization ID = FABIANA" !!!

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    There are no "nested" transactions in XA. If your program has two open XA connections, you must xa_prepare/xa_commit both of them individually.

    So you would have to describe us how connections are handled in your application. As we repeatedly told you, your application is most likely the culprit - and not DB2. (I won't rule out DB2, but I don't consider it to be very likely...)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Feb 2008
    Posts
    9
    I can't find the problem in my application!

    Hibernate should manage xa_prepare/xa_commit in transparent way... i use the methods SAVE and LOAD of the framework for persist and load data from DB2.

    Yestarday a collegue suggest me to set the following parameter of my db:

    MAXAGENTS -> 2000
    MAX_connections -> 2000
    NUM_INITAGENTS -> 20
    NUM_POOLAGENTS -> 1000

    and

    IBMDEFAULTBP --> 19968


    But never change!

  15. #15
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Hibernate is just an application - from the DB2 perspective. I suggest that you ask in some forum dedicated to Hibernate.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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