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

[SourceForge.net <no...@so...>]

Bugs item #934627, was opened at 2004-04-14 00:11
Message generated for change (Comment added) made by lithium147
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: lithium147 (lithium147)
Date: 2005-04-06 15:07

Message:
Logged In: YES 
user_id=1254099

There is a work around for this as follows:
Run the following script:
-- Query to solve MS Sql Server/OpenJMS dead lock problem
-- Trick openJMS into using no lock by create view
messages/message_handles tables
-- Rename original tables to alternate names

exec sp_rename @objname='messages',
@newname='messages_table', @objtype =  'OBJECT'
exec sp_rename @objname='message_handles',
@newname='message_handles_table', @objtype='OBJECT'

create view messages
as
 select * from messages_table with(nolock)

go

create view message_handles
as
 select * from message_handles_table with(nolock)

go


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

Comment By: Tim Anderson (tanderson)
Date: 2005-03-19 12: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 05: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