JMS PM cannot delete message

[ashields]

Occasionally the JDBC2 Persistence Manager throws the exception:

org.jboss.mq.SpyMessageException: Could not delete the message from the database: delete affected 0 rows

I've enabled trace level logging for the persistence manager, I can see the trace messages bracketing the insert as the message is posted into the queue and the trace message before the delete followed by the exception.

We have triggers on all tables (including jms_messages) that insert into a log table whenever we do an insert/update/delete, this is confirming that the insert took place but no delete.

The removal of the message from the queue is always very soon after insertion as the single consumer is doing significantly less work than the single producer. Is it possible that there is an overlap between the insert transaction and the delete transaction (possibly aggravated by our logging trigger)?

I notice that the add method gets and closes its connection object outside of the synchronized block, will the connection returned have autocommit enabled or does commit happen when the connection is closed (and returned to the pool)?

I can see two other reports of this in the forums concerning an earlier version of jboss:

here:
http://www.jboss.com/index.html?module=bb&op=viewtopic&t=49153"

and here:
http://www.jboss.com/index.html?module=bb&op=viewtopic&t=44220"

Somebody in the second thread suggests increasing the cache configuration (we are using the default) but I would have thought this would only hide the problem by not persisting in the first place, also all of our messages are persistent anyway. My understanding of the cache configuration is that if we are using -Xmx256 (or more) with the default config we just won't get any caching at all (performance is not currently a problem).

We are using:
jboss 4.0.1 SP1
java version 1.4.2 IBM AIX build xa142ifx-20050119 SR1+80507+81622 under IBM AIX 5.3
Oracle 10g - 10.1.0.4.0
Oracle thin jdbc driver ojdbc14.jar file size 1352918
Oracle JDBC2 persistence manager configuration from samples directory

I have uncommented

<attribute name="Pad">true</attribute>

in jboss-service.xml

Thanks

[adrian.brock]

I've seen this once before. It was on "clustered" Oracle where the replication
wasn't happening quick enough to keep up with the load balancing.

[ashields]

Thanks, now you point it out that seems quite likely

[atopolewski]

It seems that I have hit the same problem.

Oracle 10.2.0.3 (3 nodes RAC cluster)
RedHat x86_64 2.6.9-34.ELsmp
JBoss [Zion] 4.0.3SP1
There is no data replication of any kind in our database.

When I connect JMS pool to the RAC service that is tied to one node then the problem does not occur. When I let it run on three nodes then the exception occurs for a small percentage of the messages. Our effects are the same if we run JBoss clustered or on a single node. There is a difference in the behavior only if I let the pool run on one node or three nodes.

I have been looking on the internet for more information with no results.
Please post anything useful if you can.

Regards,
AnT