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

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: Open
>Resolution: Wont Fix
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: 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


-------------------------------------------------------
This SF.Net email is sponsored by: Oracle 10g
Get certified on the hottest thing ever to hit the market... Oracle 10g. 
Take an Oracle 10g class now, and we'll give you the exam FREE.
http://ads.osdn.com/?ad_id=3149&alloc_id=8166&op=click