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

[dlachance]

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

[kconner]

You are correct, this is a bug.

The code in SqlTableGatewayListener should be terminating the transaction after the poll.

Changing the isolation layer will allow for new rows to be inserted from another transaction but it will prevent modifications to those rows already read for the duration of the original transaction.

Can you please modify your version of SqlTableGatewayListener to release the transaction at the end of the poll and try again?

I will create a JIRA task for this.

[kconner]

I have created the following JIRA task to cover this issue.

[kconner]

I have updated code in SqlTableGatewayListener and SqlTableCourier.

It turns out that commit/rollback could have been called indirectly during the processing of a message so your suggestion should also have worked.

I have tidied up the code a bit so that all paths are now covered.