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:
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