#81 MS SQL Server very frequent deadlock victim

[Peter]

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)

[Tim Anderson]

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.

[Tim Anderson]

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.

[lithium147]

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