[AMQ-6111] LeaseDatabaseLocker does not work with Tomcat JDBC Pool

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Abandoned
• Affects Version/s: 5.12.1, 5.13.0
• Component/s: JDBC

Environment

Windows, SQLServer, HQSLDB

• Patch Info: Patch Available
• Flags: Patch
• Assignee: Unassigned
• Reporter: christian ohr
• Created: 30/Dec/15 10:54
• Updated: 13/Mar/22 14:16
• Resolved: 13/Mar/22 14:16

Description

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():

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

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.