3 Replies Latest reply on Aug 16, 2005 3:35 PM by clebert.suconic

    Locking issues with JMS

    clebert.suconic

      I'm running Specj, and I'm having locking issues on JMS. I'm using MySQL as the database.

      Caused by: org.jboss.mq.SpyJMSException: Could not remove message: 144 msg=6 hard STORED PERSISTENT queue=QUEUE.FulfillOrderQueue priority=4 lateClone=false hashCode=28033160; - nested throwable: (java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction)
       at org.jboss.mq.pm.jdbc2.PersistenceManager.remove(PersistenceManager.java:1136)
       at org.jboss.mq.server.BasicQueue.acknowledge(BasicQueue.java:564)
       at org.jboss.mq.server.JMSQueue.acknowledge(JMSQueue.java:156)
       at org.jboss.mq.server.ClientConsumer.acknowledge(ClientConsumer.java:328)
       at org.jboss.mq.server.JMSDestinationManager.acknowledge(JMSDestinationManager.java:519)
       at org.jboss.mq.server.JMSDestinationManager.transact(JMSDestinationManager.java:464)
       ... 19 more
      Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
       at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2851)
       at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
       at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1366)
       at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:952)
       at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1974)
       at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1897)
       at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1758)
       at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:227)
       at org.jboss.mq.pm.jdbc2.PersistenceManager.remove(PersistenceManager.java:1124)
       ... 24 more
      


      I'm looking for two possibilities now:

      I - If this is a know issue
      II - I'm looking also for some possible misconfiguration on MySQL


      Anyone here know if there is any know issue on locking issues on JMS and MySQL?

      I'm using READ_COMMITED as the option for my datasource. I'm also using JBOSS-4.0.3-rc2 and I have applied the changes from http://jira.jboss.org/jira/browse/JBAS-1842 just in case.


      Clebert

        • 1. Re: Locking issues with JMS

          I've seen it once before with MySQL.
          The database log showed it doing page level locking (horrible) and that caused a deadlock.
          I didn't hear how it was fixed or worked around?

          Use search for similar issues with MSSQL or Sybase.
          i.e. stupid database locking.

          Like I said to you elsewhere, you don't need any locking for JBossMQ.
          It already does the necessary isolation.
          So you could try a separate DataSource with no transaction isolation, if you don't
          want to research MySQL locking.

          No idea what JBAS-1842 has to do with this? I think you've commit-option D on the brain. :-)
          If you mean JBAS-2056 that query is only used at startup/recovery.

          • 2. Re: Locking issues with JMS
            clebert.suconic

            :-) It's very funny I have copied the wrong URL here. I really meant JBAS-2056.

            Well anyway, now I realized what you meant by using a separate data-source without transaction isolation.

            I already had anothere datasource but I though you meant to set Read_Commited instead of Repeatable_Read.

            This benchmark is driving me crazy :-)


            Thanks,


            Clebert

            • 3. Re: Locking issues with JMS
              clebert.suconic

              I tried "NO-TRANSACTION" at my JMS datasource, but MySQL doesn't support datasources with No_Transaction isolation level.

              Anyway, the problem I was seeing was just about overusing the database. We decreased the number of connections on the datasource and everything worked. We discovered that by doing

              I wanted to send this post for educational purposes, so someone could look at this if having a similar problem.