[openjms-developer] [ openjms-Bugs-934627 ] MS SQL Server very frequent deadlock victim

[SourceForge.net]

Bugs item #934627, was opened at 2004-04-14 10:11
Message generated for change (Comment added) made by tanderson
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=474136&aid=934627&group_id=54559

Category: server
Group: v0.7.6

Status: Closed
Resolution: Fixed

Priority: 5
Submitted By: Peter (peterdn1)
Assigned to: Tim Anderson (tanderson)
Summary: MS SQL Server very frequent deadlock victim

Initial Comment:
When adding a number of message to a queue getting a
frequent deadlock victim. No message are lost but this
greatly slows the rate messages can be sent to a
queue. This issue does not occur in Oracle. Tracked
the problem down to the removeMessageHandle method
in org.exolab.jms.persistence.MessageHandles class.

The select count(*) appears to lock the table in MS SQL
Server. Added 'with (NOLOCK)' to the
GET_MSG_HANDLE_COUNT

private static final String GET_MSG_HANDLE_COUNT =
"select count(messageId) from message_handles
with (NOLOCK) where messageId=?";

This resolved the issue for our application as the count
will always be 0 after the first SQL executes in this
method for our application.

15:16:00.986 ERROR [Scheduler-Worker-5] - Error in
SentMessageCache.process
org.exolab.jms.persistence.PersistenceException:
com.jnetdirect.jsql.m: Transaction (Process ID 56) was
deadlocked on lock resources with another
process and has been chosen as the deadlock victim.
Rerun the transaction.
at com.jnetdirect.jsql.af.a(Unknown Source)
at com.jnetdirect.jsql.w.a(Unknown Source)
at com.jnetdirect.jsql.w.a(Unknown Source)
at com.jnetdirect.jsql.w.executeUpdate(Unknown
Source)
at com.jnetdirect.jsql.z.executeUpdate(Unknown
Source)
at
org.apache.commons.dbcp.DelegatingPreparedStatement
.executeUpdate(DelegatingPreparedStatement.java:233)
at
org.apache.commons.dbcp.DelegatingPreparedStatement
.executeUpdate(DelegatingPreparedStatement.java:233)
at
org.exolab.jms.persistence.MessageHandles.removeMessa
geHandle(MessageHandles.java:340)
at
org.exolab.jms.persistence.RDBMSAdapter.removeMessag
eHandle(RDBMSAdapter.java:495)
at
org.exolab.jms.messagemgr.MessageHandleFactory.destr
oyPersistentHandle(MessageHandleFactory.java:361)
at
org.exolab.jms.server.SentMessageCache.process
(SentMessageCache.java:146)
at
org.exolab.jms.server.JmsServerSession.onMessage
(JmsServerSession.java:949)
at
org.exolab.jms.messagemgr.QueueConsumerEndpoint.deli
verMessages(QueueConsumerEndpoint.java:177)
at
org.exolab.jms.messagemgr.ConsumerEndpoint.run
(ConsumerEndpoint.java:425)
at
org.exolab.core.threadPool.ThreadPoolWorker.runIt
(ThreadPoolWorker.java:191)
at
org.exolab.core.threadPool.ThreadPoolWorker.runWork
(ThreadPoolWorker.java:178)
at
org.exolab.core.threadPool.ThreadPoolWorker.access$00
0(ThreadPoolWorker.java:67)
at
org.exolab.core.threadPool.ThreadPoolWorker$1.run
(ThreadPoolWorker.java:122)
at java.lang.Thread.run(Thread.java:534)





----------------------------------------------------------------------

Comment By: Tim Anderson (tanderson)

Date: 2005-03-19 23:46

Message:
Logged In: YES
user_id=557161

Fixed, as a side effect of the changes required for bug #1165743
( https://sourceforge.net/tracker/?
func=detail&atid=474136&aid=1165743&group_id=54559 )

The SQL statement executing a
"select count(messageId) from message_handles where
messageId=?"
is no longer used.

Changes have been made to CVS HEAD, and will be available in
the 0.7.7 release.


----------------------------------------------------------------------

Comment By: Tim Anderson (tanderson)
Date: 2004-05-01 15:11

Message:
Logged In: YES
user_id=557161

The fix you suggest is non-portable, as the SQL is specific to
MS SQL server. On other RDBMSes the SQL will probably
cause an SQLException.
For this reason, marking as WONT FIX unless you can come
up with another solution which doesn't break support for
other RDBMSes.



----------------------------------------------------------------------

You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=474136&aid=934627&group_id=54559