Why it is making soo many tansactions?

[Amirr Rafique]

I have created a basic Spring applicatin to read from Oracle AQ. I am experiencing following issues. Kindly help me on this

1)- Messages placed on the queue (by PL/SQL code) prior to starting of my spring application are not dequeued by it.
2)- A message that is successfull read from the queue should be removed from the queue. This is not happening. All messages remain in Queue Table.
3)- Once a message is read, the process goes in block/waiting state, as shown by the log below, if any message is place on queue during that period, that message is ignored.

2012-02-08 13:26:37,700 DEBUG [org.springframework.data.jdbc.jms.listener.oracle.AdtMessageListenerContainer] - <Received message of type [class oracle.jms.AQjmsAdtMessage] from consumer [oracle.jms.AQjmsConsumer@1989b5] of transactional session [oracle.jms.AQjmsSession@189c036]>
    2012-02-08 13:26:37,700 DEBUG [org.springframework.data.jdbc.support.oracle.BeanPropertyStructMapper] - <Mapping column 'id' to property 'id' of type class java.math.BigDecimal>
    2012-02-08 13:26:37,700 DEBUG [org.springframework.data.jdbc.support.oracle.BeanPropertyStructMapper] - <Mapping column 'type_cd' to property 'type_cd' of type class java.lang.String>
    2012-02-08 13:26:37,700 INFO [com.test.oracle.aq.ProductDelegate] - <Product [id=6, type_cd=Test Product Code]>
    2012-02-08 13:26:37,715 DEBUG [org.springframework.jdbc.core.JdbcTemplate] - <Executing SQL update [INSERT INTO Product(id, type_cd) values(6, 'Test Product Code')]>
    2012-02-08 13:26:37,731 DEBUG [org.springframework.jdbc.core.JdbcTemplate] - <SQL update affected 1 rows>
    2012-02-08 13:26:57,295 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Initiating transaction commit>
    2012-02-08 13:26:57,295 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Committing JDBC transaction on Connection [oracle.jdbc.driver.T4CConnection@1860038]>
    2012-02-08 13:26:57,295 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Releasing JDBC Connection [oracle.jdbc.driver.T4CConnection@1860038] after transaction>
    2012-02-08 13:26:57,295 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - <Returning JDBC Connection to DataSource>

4)- Why it is making soo many tansactions? While waiting on queue

2012-02-08 13:22:58,937 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Creating new transaction with name [org.springframework.data.jdbc.jms.listener.oracle.AdtMessageListenerContainer#0]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT>
    2012-02-08 13:22:59,000 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Acquired Connection [oracle.jdbc.driver.T4CConnection@9b688e] for JDBC transaction>
    2012-02-08 13:22:59,000 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Switching JDBC Connection [oracle.jdbc.driver.T4CConnection@9b688e] to manual commit>
    2012-02-08 13:23:00,045 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Initiating transaction commit>
    2012-02-08 13:23:00,045 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Committing JDBC transaction on Connection [oracle.jdbc.driver.T4CConnection@9b688e]>
    2012-02-08 13:23:00,061 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Releasing JDBC Connection [oracle.jdbc.driver.T4CConnection@9b688e] after transaction>
    2012-02-08 13:23:00,061 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - <Returning JDBC Connection to DataSource>
    2012-02-08 13:23:00,061 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Creating new transaction with name [org.springframework.data.jdbc.jms.listener.oracle.AdtMessageListenerContainer#0]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT>
    2012-02-08 13:23:00,093 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Acquired Connection [oracle.jdbc.driver.T4CConnection@eca36e] for JDBC transaction>
    2012-02-08 13:23:00,093 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Switching JDBC Connection [oracle.jdbc.driver.T4CConnection@eca36e] to manual commit>
    2012-02-08 13:23:01,154 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Initiating transaction commit>
    2012-02-08 13:23:01,154 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Committing JDBC transaction on Connection [oracle.jdbc.driver.T4CConnection@eca36e]>
    2012-02-08 13:23:01,155 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Releasing JDBC Connection [oracle.jdbc.driver.T4CConnection@eca36e] after transaction>
    2012-02-08 13:23:01,155 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - <Returning JDBC Connection to DataSource>
    2012-02-08 13:23:01,155 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Creating new transaction with name [org.springframework.data.jdbc.jms.listener.oracle.AdtMessageListenerContainer#0]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT>
    2012-02-08 13:23:01,202 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Acquired Connection [oracle.jdbc.driver.T4CConnection@19b4748] for JDBC transaction>
    2012-02-08 13:23:01,202 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Switching JDBC Connection [oracle.jdbc.driver.T4CConnection@19b4748] to manual commit>
    2012-02-08 13:23:02,263 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Initiating transaction commit>
    2012-02-08 13:23:02,263 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Committing JDBC transaction on Connection [oracle.jdbc.driver.T4CConnection@19b4748]>
    2012-02-08 13:23:02,278 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Releasing JDBC Connection [oracle.jdbc.driver.T4CConnection@19b4748] after transaction>
    2012-02-08 13:23:02,278 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - <Returning JDBC Connection to DataSource>
    2012-02-08 13:23:02,278 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Creating new transaction with name [org.springframework.data.jdbc.jms.listener.oracle.AdtMessageListenerContainer#0]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT>
    2012-02-08 13:23:02,311 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - <Acquired Connection [oracle.jdbc.driver.T4CConnection@1a41cc7] for JDBC transaction>

below is my spring config

	<bean id="dataSource" class="oracle.jdbc.pool.OracleConnectionPoolDataSource">
		<property name="URL" value="jdbc:oracle:thin:@//localhost:1521/XE" />
		<property name="user" value="migration" />
		<property name="password" value="password" />
	</bean>
	<bean id="transactionManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
		lazy-init="true">
		<property name="dataSource" ref="dataSource" />
	</bean>
	<bean id="messageDelegate" class="com.test.oracle.aq.ProductDelegate" />
	<jms:listener-container connection-factory="connectionFactory"   
        transaction-manager="transactionManager"
        message-converter="messageConverter" 
        container-class="org.springframework.data.jdbc.jms.listener.oracle.AdtMessageListenerContainer" destination-type="topic">
        <jms:listener destination="MIGRATION.PRODUCT_QUEUE" ref="messageDelegate" method="handleMessage" />
    </jms:listener-container>
    <bean id="messageConverter"                                                          
            class="org.springframework.data.jdbc.jms.support.converter.oracle.MappingAdtMessageConverter">
        <constructor-arg>
            <bean class="org.springframework.data.jdbc.jms.support.oracle.StructDatumMapper">
                <constructor-arg index="0" value="MIGRATION.PRODUCT_T"/>
                <constructor-arg index="1" value="com.test.oracle.aq.Product"/>
            </bean>
        </constructor-arg>
    </bean>
	<orcl:aq-jms-connection-factory id="connectionFactory" data-source="dataSource"/> 
	<bean id="jmsTemplate" class="org.springframework.jms.core.JmsTemplate">
		<property name="connectionFactory" ref="connectionFactory" />
		<property name="sessionTransacted" value="true"/>
 	</bean>

Kindly help me out
Thanks

[Amirr Rafique]

any idea guys

[Mark Spritzler]

In your message listener bean you have

container-class="org.springframework.data.jdbc.jms.listener.oracle.AdtMessageListenerContainer" destination-type="topic">

destination-type="topic" but you say it is a Queue. Queues and Topics work differently, Topics can keep messages on the Topic after just one listener receives it.

Maybe that is why.

Mark

[Amirr Rafique]

Hi Mark,
it is a Topic but even if there is no other listener and no new message on Topic, my transaction manager keeps on creating transaction.....

[Mark Spritzler]

You are not making sense, in your first post you say you are putting a message on the Queue, then it is a Topic, then there is no messages.

Do you have your Topic configured to be a persistent one, so that it uses a database to save Messages? Maybe that is causing it.


Maybe it is not the Topic creating Transactions but some other code. You aren't using any Scheduler?

It also looks like the Transaction is committing something, so what query is running?

Unfortunately, this might be a case of there is too much information missing to help, and too much too post. In a forum, and might require someone with eyes on it to help fix it.

Good Luck.

Thanks

Mark