Using Jboss 4.0.3 SP1 with Sybase anywhere version 9.0.2 (3302) it throws a
deadlock exception when using JMS.
The configuration is as follows:
1) In the Sybase datasource file the transaction-isolation is set to
TRANSACTION_READ_COMMITTED
2) Set the Sybase isolation_level to 1
3) Created additional indexes on the JMS_MESSAGES table. (No effect)
4) The LOCK DATAROWS option is not available in Sybase anywhere. Is
there a similar configuration paramater?
5) Remove the UNION in the SELECT_MAX_TX sql command. (No effect)
Any assistance on this would be greatly appreciated.
sybasedb-jdbc2-service.xml
BLOB_TYPE=BYTES_BLOB
INSERT_TX = INSERT INTO JMS_TRANSACTIONS (TXID) values(?)
INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION,
MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS
SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_TRANSACTIONS UNION SELECT
MAX(TXID) FROM JMS_MESSAGES
DELETE_ALL_TX = DELETE FROM JMS_TRANSACTIONS
SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES
WHERE DESTINATION=?
SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE
MESSAGEID=? AND DESTINATION=?
MARK_MESSAGE = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=?
AND DESTINATION=?
UPDATE_MESSAGE = UPDATE JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=?
AND DESTINATION=?
UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE
TXOP=?
UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGES SET TXID=?, TXOP=?
WHERE TXOP=? AND TXID=?
DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGES WHERE TXOP=?
AND JMS_MESSAGES.TXID IN (SELECT TXID FROM JMS_TRANSACTIONS)
DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ?
DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=?
DELETE_TEMPORARY_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXOP='T'
DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND
DESTINATION=?
CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT
NULL, \
DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER NULL, TXOP CHAR(1), \
MESSAGEBLOB IMAGE, PRIMARY KEY (MESSAGEID, DESTINATION) )
CREATE_IDX_MESSAGE_TXOP_TXID = CREATE INDEX JMS_MESSAGES_TXOP_TXID ON
JMS_MESSAGES (TXOP, TXID)
CREATE_IDX_MESSAGE_DESTINATION = CREATE INDEX JMS_MESSAGES_DESTINATION ON
JMS_MESSAGES (DESTINATION)
CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER, PRIMARY
KEY (TXID) )
CREATE_TABLES_ON_STARTUP = TRUE
org.jboss.mq.SpyJMSException: Could not remove message: 2188 msg=800 hard
STORED PERSISTENT queue=QUEUE.B priority=4 lateClone=false hashCode=8938306; -
nested throwable: (com.sybase.jdbc2.jdbc.SybSQLException: ASA Error -306:
Deadlock detected)
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)
at
org.jboss.mq.server.JMSServerInterceptorSupport.transact(JMSServerInterceptorSupport.java:186)
at
org.jboss.mq.security.ServerSecurityInterceptor.transact(ServerSecurityInterceptor.java:182)
at
org.jboss.mq.server.TracingInterceptor.transact(TracingInterceptor.java:412)
at
org.jboss.mq.server.JMSServerInvoker.transact(JMSServerInvoker.java:186)
at
org.jboss.mq.il.uil2.ServerSocketManagerHandler.handleMsg(ServerSocketManagerHandler.java:172)
at
org.jboss.mq.il.uil2.SocketManager$ReadTask.handleMsg(SocketManager.java:369)
at org.jboss.mq.il.uil2.msgs.BaseMsg.run(BaseMsg.java:377)
at
EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:748)
at java.lang.Thread.run(Thread.java:595)
Caused by: com.sybase.jdbc2.jdbc.SybSQLException: ASA Error -306: Deadlock
detected
at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2538)
at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1922)
at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:201)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:182)
at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1611)
at
com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1594)
at
com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:89)
at
org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:227)
at
org.jboss.mq.pm.jdbc2.PersistenceManager.remove(PersistenceManager.java:1124)
... 14 more
View the original post :
http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3951296#3951296
Reply to the post :
http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3951296
_______________________________________________
JBoss-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/jboss-user
