Announcement Announcement Module
Collapse
No announcement yet.
Container freezes with Local TX on Oracle + Postgresql Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Container freezes with Local TX on Oracle + Postgresql

    Hi all,

    I'm about to deliver the system and while doing performance tests for the acceptance we are seeing a very *VERY* weird problem.
    Our system controls the creation of users in two different DB's: one Oracle RAC and one PostgreSQL. None of them support XA so we use Spring Transactions (Local, JDBC based) to control the TX's to the DB's
    The interface is written in PL/SQL, so to create an user, we call a PL/SQL on Oracle and one on Postgress. If everything is OK we do a 1PC.

    All our functional tests are 100% OK but when doing performance tests, our APP Server started to block at regular intervals. The block freezes everything (but it's not the GC. I checked!) The last log entry before the freeze time is:
    2005-03-04 21:13:19,128 INFO [ExecuteThread: '14' for queue: 'weblogic.kernel.Default'] support.AbstractPlatformTransactionManager (AbstractPlatformTransaction
    Manager.java:315) - Initiating transaction commit

    The freeze takes 2.2 minutes and then the appserv resumes operations.

    I checked my config and found that the StoredProcedures where declared singleton in the spring config. With the Stored Proc as singleton, the freeze happens at intervals of +- 240 calls. Changing them to 'prototype' makes it much better, but I still get a lock at about +- 2500 calls
    (1 call = TX on Oracle + 1 TX on Postgress)
    There are no exceptions or stack traces; after the freeze the app resumes "business as usual" and after a while stops again. (I repeat: it's not the GC!!! :-)

    I've to deliver the system next week and I can't find a clue. My guess is that it has something to deal with the way that Spring 'hangs' the TxManager to the thread (I've to 'hang' two, as I've to local - nested transactions)
    Simplistic schema:
    TX1 start
    TX2 start
    result1= OP1
    result2=OP2
    if (result1==ok & result2 ==OK)
    TX2 commit
    TX1 commit
    else
    TX2 rollback
    TX1 rollback

    Where do I have to start looking?? Any hints???? HEEEELPPP!!! PLEEASE :-)

    -greetings,

    Gerard.

  • #2
    I'd start by seeing what happens when you post to just the Oracle DB, and then just the postgres. That would ensure that the problem only exists when both are being used.
    If you get lucky, you'll find that it's an issue with one of the drivers or db's that you can fix easily. If you're not so lucky and it's the two drivers interacting weirdly.

    Comment


    • #3
      Done that, but doesn't help.

      Originally posted by GrimShieldsson
      I'd start by seeing what happens when you post to just the Oracle DB, and then just the postgres.
      Thanks Grim.
      Yes, I've done that already and going to one or the other poses no problem at all. It's when I go to both that the weird problem shows its ugly face.
      I've strong suspicions that it's something under the hood of the DataSourceTransactionManager and how it hangs on the thread. Maybe I'm having some sort of deadlock, but it's weird that it comes loose by itself.
      BTW, I also stubbed the DB so there're no row locks playing havoc on me.
      It has to be at the level of the start-commit/rollback somewhere....

      any hints???

      -regards.
      Gerard.

      Comment


      • #4
        Other then the less then useful, have fun with your debugger going through the driver code.. probably not.

        However. just before you go into this, how many threads do you have, and how many do you have while going through? If you have a thread for each db call, your looking for something outside your software I'm thinking. If only one (as I suspect) you're right back where yous tarted.

        If you're just running a test, slamming all the transactions one after the other (or all at once) I'd try seeing what happens if I put say, 5 seconds between the requests, what happens? If it works just fine then you're overrunning a buffer, the transactions are blocking until the buffer gets caught up. You might also see this if it happens every 240 callls. I'm thining you've tried that though.

        If you haven't tried running this withoutt the spring transaction stuff, then try that.

        Comment


        • #5
          Just an idea - because the freeze takes 2.2 minutes I think there is some transaction deadlock which stops due to timeout - search inside the configuration and see what happens if you modify the transactions timeout.
          Can you also post your configuration (database version and driver)? Also try playing with the different jdbc drivers (in case you already have the latest versions).
          Don't forget to post the solution when you find it .

          Comment


          • #6
            Solved!!!

            Hi!
            Thanks a lot for the great advise. I've to say that you where both right!
            I turned JDBC logging on [info level] and start seeing Exceptions flying arround (well.... it was so late that I was seeing ALL kind of things flying arround)
            I googled the exception message and started finding references to a Network Deadlock problem with Postgresql.
            I leave here some refs in case somebody else hits the head to this d**m bug:
            http://jdbc.postgresql.org/todo.html
            http://archives.postgresql.org/pgsql...2/msg00160.php

            I upgraded the driver and now everything works fine.

            This saved the intro of Spring in our project altogether as this problem started showing after I replaced the WLS JTA transactions with Spring JDBC in a critical moment for the project. Of course we (spring & me :-) were the ugly suspects for this bug.


            Kind regards, Gerard.

            Comment


            • #7
              Once thing that I totally love about Spring is that you have control over your actions like transactions (as opposed to application server) and you can configure them as you please.
              It's very refreshing for me at least when I saw how easy it was to make certain scenarios in my tests to test how transactions work under concurrent users with Spring and Hibernate as oppose to JBoss and Hibernate.
              Simpler is better ! (tm)

              Comment

              Working...
              X