[
https://issues.apache.org/activemq/browse/AMQ-1191?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_40321
]
Andreas Guther commented on AMQ-1191:
-------------------------------------
>From our database admin I got the following suggestion to fix the problem
>which I would like to share here. I hope that helps in some way.
Here is his comment:
Hmm, I suspect this is just a poor locking protocol. It looks like they are
doing a read, will examine the results and then write something. We could
probably create a nice database invariant solution that doesn't require complex
locking. Otherwise what you want to do is fix the problem where the transaction
is started. You need a serializable transaction from the start. All DB's will
support that and you should be able to set it as a JDBC option.
(from transact-SQL: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE)
Assuming I'm correct about the way they are attempting to acquire the lock, a
better way would be to construct an UPDATE instead of a SELECT. So, if the
underlying code does this select and then does an update what really should
happen is:
Begin tran
UPDATE
SET
Time =
,broker_name =
From
Dbo.activemq_lock
Where
Id=1
And broker_name is null --- Or whatever
additional criteria there update uses.
Commit
With this approach you don't need to change the isolation level and you are
still guaranteed only one writer can perform the update. You can check for
success in one of two ways. Get the number of rows affected by the update or do
a SELECT to see if the broker_name in the table is yours.
> JDBC based Master/Slave not supported for TransactSQL based databases (SQL
> Server and Sybase)
> ---------------------------------------------------------------------------------------------
>
> Key: AMQ-1191
> URL: https://issues.apache.org/activemq/browse/AMQ-1191
> Project: ActiveMQ
> Issue Type: Improvement
> Components: Broker
> Reporter: James Strachan
> Assignee: James Strachan
> Fix For: 5.1.0
>
> Attachments: patchfile
>
>
> The main issue is figuring out the exclusive lock SQL syntax. I think the
> following is valid...
> SELECT * FROM TABLE WITH XLOCK
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
