You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@activemq.apache.org by mikmela <mi...@yahoo.com> on 2012/06/25 18:32:37 UTC

Problem with TransactDatabaseLocker's lock statement

I'm not sure if it's the specific version of SQL server that we're using, but 
S*ELECT * FROM ACTIVEMQ_LOCK WHERE ID=1 FOR UPDATE* throws exception *"FOR
Update clause allowed only for DECLARE CURSOR" *...
I'm using 5.6.0 - Am I missing something in connection string or
configuration properties? 

--
View this message in context: http://activemq.2283324.n4.nabble.com/Problem-with-TransactDatabaseLocker-s-lock-statement-tp4653552.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.

Re: Problem with TransactDatabaseLocker's lock statement

Posted by mikmela <mi...@yahoo.com>.
Thanks for pointing to this issue.
But the method that overrides lock create statement has a bug... If
isUseLockCreateWhereClause() is true, the produced statement will look
something like this:
*SELECT * FROM ACTIVEMQ_LOCK WHERE ID=1 WITH (UPDLOCK, ROWLOCK)* which will
throw a 
 */com.microsoft.sqlserver.jdbc.SQLServerException: A processing error
"Incorrect syntax near '('." has occurred/*... with MS SQL Server 2008 and
up... I guess it was tested with earlier versions which were more loose in
terms syntax... 
The right way is 
*SELECT * FROM ACTIVEMQ_LOCK WITH (UPDLOCK, ROWLOCK) WHERE ID=1 *

    public void setStatements(Statements statements) {
        String lockCreateStatement = "SELECT * FROM " +
statements.getFullLockTableName();

        if (statements.isUseLockCreateWhereClause()) {
            lockCreateStatement += " WHERE ID = 1";
        }
        lockCreateStatement += " WITH (UPDLOCK, ROWLOCK)";
        statements.setLockCreateStatement(lockCreateStatement);

        super.setStatements(statements);
    }

--
View this message in context: http://activemq.2283324.n4.nabble.com/Problem-with-TransactDatabaseLocker-s-lock-statement-tp4653552p4653776.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.

Re: Problem with TransactDatabaseLocker's lock statement

Posted by Gary Tully <ga...@gmail.com>.
have a peek at https://issues.apache.org/jira/browse/AMQ-1191

On 6 July 2012 16:36, mikmela <mi...@yahoo.com> wrote:
> Have you seen this simple select *SELECT * FROM ACTIVEMQ_LOCK WHERE ID=1*
> working for MS SQL Server?
> It doesn't for me in terms of master/slave synchronization (i.e. slave is
> not blocked on this select) unless I do something like this *SELECT * FROM
> ACTIVEMQ_LOCK(rowlock, updlock, REPEATABLEREAD) WHERE ID=1*...
>
> If anyone have successful implementation of MASTER/SLAVE configuration for
> MS MSQL Server, please share details...
>
> --
> View this message in context: http://activemq.2283324.n4.nabble.com/Problem-with-TransactDatabaseLocker-s-lock-statement-tp4653552p4653773.html
> Sent from the ActiveMQ - User mailing list archive at Nabble.com.



-- 
http://fusesource.com
http://blog.garytully.com

Re: Problem with TransactDatabaseLocker's lock statement

Posted by mikmela <mi...@yahoo.com>.
Have you seen this simple select *SELECT * FROM ACTIVEMQ_LOCK WHERE ID=1*
working for MS SQL Server?
It doesn't for me in terms of master/slave synchronization (i.e. slave is
not blocked on this select) unless I do something like this *SELECT * FROM 
ACTIVEMQ_LOCK(rowlock, updlock, REPEATABLEREAD) WHERE ID=1*...

If anyone have successful implementation of MASTER/SLAVE configuration for
MS MSQL Server, please share details...

--
View this message in context: http://activemq.2283324.n4.nabble.com/Problem-with-TransactDatabaseLocker-s-lock-statement-tp4653552p4653773.html
Sent from the ActiveMQ - User mailing list archive at Nabble.com.

Re: Problem with TransactDatabaseLocker's lock statement

Posted by Gary Tully <ga...@gmail.com>.
The lock implementation is picked up from the driver name but there is
currently no variant that does not use the "FOR UPDATE"

If one particular statement is causing problems, you can override the
default to drop the 'FOR UPDATE' using xml config:

 <persistenceAdapter>
           <jdbcPersistenceAdapter dataDirectory=".." lockKeepAlivePeriod="..">
               <statements>
                   <statements
                           lockCreateStatement="SELECT * FROM
ACTIVEMQ_LOCK"
                           stringIdDataType=".." >
                   </statements>
               </statements>
           </jdbcPersistenceAdapter>
       </persistenceAdapter>

On 25 June 2012 17:32, mikmela <mi...@yahoo.com> wrote:
> I'm not sure if it's the specific version of SQL server that we're using, but
> S*ELECT * FROM ACTIVEMQ_LOCK WHERE ID=1 FOR UPDATE* throws exception *"FOR
> Update clause allowed only for DECLARE CURSOR" *...
> I'm using 5.6.0 - Am I missing something in connection string or
> configuration properties?
>
> --
> View this message in context: http://activemq.2283324.n4.nabble.com/Problem-with-TransactDatabaseLocker-s-lock-statement-tp4653552.html
> Sent from the ActiveMQ - User mailing list archive at Nabble.com.



-- 
http://fusesource.com
http://blog.garytully.com