[jira] [Comment Edited] (AMQ-6111) LeaseDatabaseLocker does not work with Tomcat JDBC Pool

["christian ohr (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/AMQ-6111?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15075001#comment-15075001 ] 

christian ohr edited comment on AMQ-6111 at 12/30/15 12:46 PM:
---------------------------------------------------------------

Also think about increasing the amount of threads working on the inbound message queues (see screenshot). 
Not sure how the current setting is. I would not go much beyond 20, though.

Note that this change would require a restart of the MPI


was (Author: christian.ohr):
Could also think about increasing the amount of threads working on the inbound message queues. Not sure how the current setting is (see screenshot). Note that this change requires a restart of the MPI

> LeaseDatabaseLocker does not work with Tomcat JDBC Pool
> -------------------------------------------------------
>
>                 Key: AMQ-6111
>                 URL: https://issues.apache.org/jira/browse/AMQ-6111
>             Project: ActiveMQ
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions: 5.12.1, 5.13.0
>         Environment: Windows, SQLServer, HQSLDB
>            Reporter: christian ohr
>            Priority: Minor
>         Attachments: AMQ-6111.patch, matcher-idefix-threads.png
>
>
> When using the LeaseDatabaseLocker with the Tomcat JDBC Pool (org.apache.tomcat.jdbc.pool.DataSource), the startup hangs. I tested this on a variety of databases (SQLServer, Oracle, HSQLDB).
> This happens when first entering the keepAlive() method from within doStart():
> {code}
> ...
> 2015-12-30 10:42:27,848 | DEBUG | Could not create JDBC tables; The message table already existed. Failure was: CREATE TABLE ACTIVEMQ_LOCK( ID BIGINT NOT NULL, TIME BIGINT, BROKER_NAME VARCHAR(250), PRIMARY KEY (ID) ) Message: There is already an object named 'ACTIVEMQ_LOCK' in the database. SQLState: S0001 Vendor code: 2714 | org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter | main
> 2015-12-30 10:42:27,848 | DEBUG | Executing SQL: INSERT INTO ACTIVEMQ_LOCK(ID) VALUES (1) | org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter | main
> 2015-12-30 10:42:27,849 | DEBUG | Could not create JDBC tables; The message table already existed. Failure was: INSERT INTO ACTIVEMQ_LOCK(ID) VALUES (1) Message: Violation of PRIMARY KEY constraint 'PK__ACTIVEMQ__3214EC272E692261'. Cannot insert duplicate key in object 'dbo.ACTIVEMQ_LOCK'. The duplicate key value is (1). SQLState: 23000 Vendor code: 2627 | org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter | main
> 2015-12-30 10:42:27,852 | INFO  | broker1 attempting to acquire exclusive lease to become the master | org.apache.activemq.store.jdbc.LeaseDatabaseLocker | main
> 2015-12-30 10:42:27,853 | DEBUG | broker1 locking Query is UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=?, TIME=? WHERE (TIME IS NULL OR TIME < ?) AND ID = 1 | org.apache.activemq.store.jdbc.LeaseDatabaseLocker | main
> 2015-12-30 10:42:27,859 | DEBUG | broker1, lease keepAlive Query is UPDATE ACTIVEMQ_LOCK SET BROKER_NAME=?, TIME=? WHERE BROKER_NAME=? AND ID = 1 | org.apache.activemq.store.jdbc.LeaseDatabaseLocker | main
> ... (and here it stops) ...
> {code}
> Funny enough, I cannot observe this behavior, when createTablesOnStartup is set to false (if the tables do already exist). Also when using normal DBCP or DBCP2 pools, this behavior does not occur.
> Looking for a reason, I noticed that doStart() updates the lock table (without a commit) and keepAlive() tries to update it again, but with a *different connection*. I guess somehow the Tomcat JDBC pool prevents uncommitted reads, and I wonder why this is necessary anyway.
> So I wrote a custom LeaseDatabaseLocker that reuses the first connection when calling keepAlive() the first time, and everything works fine.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)