3 Replies Latest reply on Apr 24, 2007 12:28 PM by Kevin Conner

    Newbie: jbossesb-4.0.GA: quickstarts/helloworld_sql_action -

    Daniel LaChance Newbie

      I'm having a problem running the quickstarts/helloworld_sql_action sample and wondering if someone might help solve it.

      I'm using the StandAloneBootStrapper (default ant task "run") to start the ESB for the sample.

      Without the ESB running I can use the Ant tasks to modify the "test_sql_gateway" database in MySQL, inserting the sample messages with the Ant "runtest" task fine. And I've verified that the listenser is able to connect to the MySQL database when there are 'pending' messages already in the "gateway_table" table.

      However, when the ESB is running, the database connection has locked the table preventing further rows from being inserted into "gateway_table" table from another client, resulting in the Ant "runtest" task return the error:

      C:\java\jbossesb\jbossesb-4.0.GA\samples\quickstarts\helloworld_sql_action>ant runtest
      Buildfile: build.xml
      
      runtest:
       [echo] Insert row data into sql table polled by gateway
       [exec] ERROR 1205 (HY000) at line 2: Lock wait timeout exceeded; try restarting transaction
       [exec] Result: 1
      
      BUILD SUCCESSFUL
      Total time: 51 seconds
      


      After inspecting the code for the classes:
      org.jboss.soa.esb.listeners.gateway.SqlTableGatewayListener.java and
      org.jboss.soa.esb.helpers.persist.JdbcCleanConn.java,
      I suspect this is due to the connection transaction isolation level set in JdbcCleanConn,

      m_conn.setAutoCommit(false);
      m_conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
      


      Since the connection is open for the life of the listener, having this isolation level would explain the table locking issue, but I would guess someone has/had this sample working at some time.

      I also suspect (have not tested) that if the transaction isolation level was changed to TRANSACTION_REPEATABLE_READ that it may work.

      (I also had a look at the source code from 4.2MR1 but did not see any changes to the isolation level in JdbcCleanConn.)


      Specs:
      jboss-4.0.5.GA
      jbossesb-4.0.GA
      MySQL Server 5.0.27-community-nt
      driver JAR: mysql-connector-java-5.0.5-bin.jar