5 Replies Latest reply on Jan 20, 2005 10:31 AM by ericmolitor

JBoss 4.0/SQL Server Lock Condition

kevindaly Dec 17, 2004 10:06 AM

Hi,

We are in the process of migrating our AppServer from JBoss 3.2.3 to
JBoss 4.0. Everything deploys and runs pretty painlessly but we are
encountering problems with our MDBs. The MDBs consume messages
for a brief while before throwing exceptions citing a dblock condition.

Our setup is pretty much out of the box, we are using the UI2Service
with a PersistanceManager setup of:

<depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=CeltrakJMSData

BLOB_TYPE=BINARYSTREAM_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_MESSAGES
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=?
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 TXID IN (SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP=?
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(150) NOT NULL, TXID INTEGER, 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

We are using a SQL Server database as the exceptions will indicate. I can
include more config files if it would help.

Has anyone seen anything similar ?

- Kevin.

2004-12-17 14:48:21,556 WARN [org.jboss.tm.TransactionImpl] XAException: tx=TransactionImpl:XidImpl[FormatId=257, GlobalId=BEAKER/68, BranchQual=, localId=68] errorCode=XAER_RMERR
org.jboss.mq.SpyXAException: - nested throwable: (org.jboss.mq.SpyTransactionRolledBackException: Transaction was rolled back.; - nested throwable: (org.jboss.mq.SpyJMSException: Could not remove message: 16 msg=15 hard STORED PERSISTENT queue=TOPIC.GPRS.DurableSubscription[clientId=GPRSID name=GPRSDataSubscriber selector=MessageRef='unprocessed sms response'] priority=4 lateClone=false hashCode=24007705; - nested throwable: (java.sql.SQLException: [Microsoft][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.)))
at org.jboss.mq.SpyXAResource.prepare(SpyXAResource.java:135)
at org.jboss.tm.TransactionImpl$Resource.prepare(TransactionImpl.java:2100)
at org.jboss.tm.TransactionImpl.prepareResources(TransactionImpl.java:1552)
at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:319)
at org.jboss.tm.TxManager.commit(TxManager.java:200)
at org.jboss.jms.asf.StdServerSession.onMessage(StdServerSession.java:341)
at org.jboss.mq.SpyMessageConsumer.sessionConsumerProcessMessage(SpyMessageConsumer.java:877)
at org.jboss.mq.SpyMessageConsumer.addMessage(SpyMessageConsumer.java:159)
at org.jboss.mq.SpySession.run(SpySession.java:351)
at org.jboss.jms.asf.StdServerSession.run(StdServerSession.java:180)
at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:743)
at java.lang.Thread.run(Thread.java:536)
Caused by: org.jboss.mq.SpyTransactionRolledBackException: Transaction was rolled back.; - nested throwable: (org.jboss.mq.SpyJMSException: Could not remove message: 16 msg=15 hard STORED PERSISTENT queue=TOPIC.GPRS.DurableSubscription[clientId=GPRSID name=GPRSDataSubscriber selector=MessageRef='unprocessed sms response'] priority=4 lateClone=false hashCode=24007705; - nested throwable: (java.sql.SQLException: [Microsoft][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.))
at org.jboss.mq.server.JMSDestinationManager.transact(JMSDestinationManager.java:478)
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:438)
at org.jboss.mq.server.JMSServerInvoker.transact(JMSServerInvoker.java:186)
at org.jboss.mq.il.uil2.ServerSocketManagerHandler.handleMsg(ServerSocketManagerHandler.java:166)
at org.jboss.mq.il.uil2.SocketManager$ReadTask.handleMsg(SocketManager.java:356)
at org.jboss.mq.il.uil2.msgs.BaseMsg.run(BaseMsg.java:377)
... 2 more

1. Re: JBoss 4.0/SQL Server Lock Condition

adrian.brock Dec 17, 2004 11:58 AM (in response to kevindaly)

I don't see any reason why JMS persistence should cause a deadlock.

No part of the system should be trying to update the same data at the same time.
If it were, it would be a bug.

The most likely cause is that the stupid locking mechanism within MSSQL
is taking locks that are not row locks (e.g. full table or column locks).
Actions
2. Re: JBoss 4.0/SQL Server Lock Condition

ericmolitor Jan 3, 2005 12:20 PM (in response to kevindaly)

JBoss 3.2.6 does this as well. If you play around with clustered indexes you can make it go away, however JBoss does access the DB in a non-optimal way (at least from SQL Servers perspective). I've been meaning to fix this (or at least supply a working XML file for SQL server) but dont have the time right now. Dont know if its documented but if you are using 3.2.6 or 4.0, or 4.0.1 (and probably 3.2.5) I highly sugest not using SQL server for JMS persistance.

The longer term answer is to profile and look at how JBoss creates, selects and deletes message with regards to tx_id's. We've seen jboss delete all messages with a tx_id while its still trying to insert them. However thats from SQL's perspective and since we moved to a different JMS setup we no longer see these issues and only saw them while under load.

Funny thing is you can reproduce this with even the singleton JMS if you pump thousands of small messages through.
Actions
3. Re: JBoss 4.0/SQL Server Lock Condition

genman Jan 4, 2005 2:22 PM (in response to kevindaly)

Even with a singleton, the server/client thread is doing inserts into the DB for the enqueue operation, the MDB thread is doing deletes and optionally inserts (if your MDB sends to another queue.) The message cache also has its own thread.
Actions
4. Re: JBoss 4.0/SQL Server Lock Condition

adrian.brock Jan 5, 2005 3:11 PM (in response to kevindaly)

But none of them are looking at the same rows in the database on different threads/transactions.
It is just crappy locking in MSSQL.
Actions
5. Re: JBoss 4.0/SQL Server Lock Condition

ericmolitor Jan 20, 2005 10:31 AM (in response to kevindaly)

A further explanation than crappy locking (though I agree) is that the locks escalate to page level locks and if you have short fast running JMS operations the client conflicts with the consumer leading to the optimizer picking a plan that causes the deadlocks. One work around (although you're probably better off switching to a different method of JMS serialization) is to adjust your fill factor so that only one record ends up on each page. This will trade in the deadlocks for higher memory utilization and higher cpu (caused be more memory management)
Actions

Go to original post