activemq-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andreas Guther (JIRA)" <j...@apache.org>
Subject [jira] Commented: (AMQ-1191) JDBC based Master/Slave not supported for TransactSQL based databases (SQL Server and Sybase)
Date Tue, 09 Oct 2007 15:26:24 GMT

    [ 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.


Mime
View raw message