Jdbc Acknowledgment (ACK) in Mule

Question

I like to update my database column deltaprocessstatus as 'I' after fetching the data from database.

Below is my code

<spring:beans> 
    <context:property-placeholder location="classpath:mule-app.properties"/>
    </spring:beans>
    <jdbc-ee:mssql-data-source name="MS_SQL_Data_Source" user="Ssssss" password="ffff" url="jdbc:sqlserver://localhost" transactionIsolation="UNSPECIFIED" doc:name="MS SQL Data Source"/>
    <jdbc-ee:connector name="ProSFDB" dataSource-ref="MS_SQL_Data_Source" validateConnections="true" queryTimeout="-1" pollingFrequency="0" doc:name="Database" transactionPerMessage="false">
        <jdbc-ee:query key="SelectAllUser" value="SELECT top 100 * from master_user_Queue"/>
        <jdbc-ee:query key="SelectAllUser.ack" value="update master_user_Queue set DeltaProcessStatus = 'I' where MasterUserID = 1"/>
        <jdbc-ee:query key="SelectbatchWiseInfo" value="Select top 100 * from  master_user_Queue where MasterUserID between #[payload['min']] and #[payload['max']]"/>

    </jdbc-ee:connector>
    <amqp:connector name="AMQP_Connector" validateConnections="true" host="${amqp.host}" port="${amqp.port}" virtualHost="${amqp.virtualhost}" username="${amqp.username}" password="${amqp.password}" doc:name="AMQP Connector"/>

    <spring:beans>
        <spring:bean id="objectStore" name="Bean" class="org.mule.util.store.SimpleMemoryObjectStore"/>       
    </spring:beans>

    <data-mapper:config name="Map_To_Map" transformationGraphPath="map_to_map.grf" doc:name="Map_To_Map"/>

    <flow name="asi_1_user_deactivationFlow1" doc:name="asi_1_user_deactivationFlow1" processingStrategy="synchronous">
        <jdbc-ee:inbound-endpoint exchange-pattern="one-way"  queryTimeout="-1" pollingFrequency="6000" doc:name="Database" connector-ref="ProSFDB" queryKey="SelectAllUser" tracking:enable-default-events="true">
            <jdbc-ee:transaction action="ALWAYS_BEGIN"/>
        </jdbc-ee:inbound-endpoint>
</flow>

the query key "SelectAllUser" is working fine but the querykey "SelectAllUser.ack" is not working once the "SelectAllUser" is called. I don't know where I am going wrong.

Thanks in advance.

Answer 1

The SelectAllUser is missing a where clause that would make SelectAllUser.ack useful.

Indeed, the ack query sets DeltaProcessStatus = 'I' but the select query doesn't check that DeltaProcessStatus != 'I'. Therefore the same rows will be selected again and again.

Also the fact that the ack query as a where clause with MasterUserID = 1 but not the select query is highly suspicious. There's a risk the updated rows are not matching the selected ones.

EDIT: Another potential issue you are having is that you are starting a transaction in the inbound endpoint but there is nothing that concludes this transaction since the flow is empty. Can you try removing the transaction element? It is useless anyway as long as the flow is empty...