Yes I had set the connection autoCommit value to false.
Below is the complete code snippet:
Connection connection = null;
PreparedStatement preparedStatement = null;
PreparedStatement contentRemovalPreparedStatment = null;
Context context =
DataAccessMatrixManager.addAndGetTimer(MatrixConstants.DELETE_MESSAGE_META_DATA_FROM_QUEUE,
this).
start();
try {
int queueID = getCachedQueueID(storageQueueName);
connection = getConnection();
connection.setAutoCommit(false);
preparedStatement = connection
.prepareStatement(RDBMSConstants.PS_DELETE_METADATA_FROM_QUEUE);
contentRemovalPreparedStatment =
connection.prepareStatement(RDBMSConstants.PS_DELETE_MESSAGE_PARTS);
for (AndesRemovableMetadata md : messagesToRemove) {
//add parameter to delete metadata
preparedStatement.setInt(1, queueID);
preparedStatement.setLong(2, md.getMessageID());
preparedStatement.addBatch();
//add parameters to delete content
contentRemovalPreparedStatment.setLong(1, md.getMessageID());
contentRemovalPreparedStatment.addBatch();
}
preparedStatement.executeBatch();
contentRemovalPreparedStatment.executeBatch();
connection.commit();
//TODO find a better phrase
if (log.isDebugEnabled()) {
log.debug("Metadata and content removed. " + messagesToRemove.size() +
" metadata from destination " + storageQueueName);
}
} catch (SQLNonTransientConnectionException sqlConEx) {
rollback(connection,
RDBMSConstants.TASK_DELETING_METADATA_FROM_QUEUE + storageQueueName
+ " and " + RDBMSConstants.TASK_DELETING_MESSAGE_PARTS);
throw new AndesStoreUnavailableException("error occurred while
deleting message metadata " +
"and content for queue " + storageQueueName,
sqlConEx.getSQLState(), sqlConEx);
} catch (SQLException e) {
rollback(connection,
RDBMSConstants.TASK_DELETING_METADATA_FROM_QUEUE + storageQueueName
+ " and " + RDBMSConstants.TASK_DELETING_MESSAGE_PARTS);
throw new AndesException("error occurred while deleting message
metadata and content for queue ",
e);
} finally {
context.stop();
String task = RDBMSConstants.TASK_DELETING_METADATA_FROM_QUEUE +
storageQueueName;
String messageContentRemovalTask =
RDBMSConstants.TASK_DELETING_MESSAGE_PARTS;
close(preparedStatement, task);
close(contentRemovalPreparedStatment, messageContentRemovalTask);
close(connection, task + " and " + messageContentRemovalTask);
}
Thanks
On Wed, May 27, 2015 at 1:12 PM, Harsha Kumara <hars...@wso2.com> wrote:
> Hi Sasikala,
>
> Can you share your full code snippet. I hope you have initially set
> connection autoCommit value to false. As Lahiru mentioned, it would be best
> to have foreign key constraint with cascade delete.
>
> Thanks,
> Harsha
>
>
>
> On Wed, May 27, 2015 at 10:20 AM, Sasikala Kottegoda <sasik...@wso2.com>
> wrote:
>
>> Hi all,
>>
>> MB stores message metadata and content in two separate tables.
>>
>> Currently, message metadata is being deleted once the messages are
>> delivered, but the content is not (deleted later by a scheduled task).
>>
>> What I'm trying to do is to delete (transactional delete) both message
>> metadata and content once they get delivered. For this, I have created two
>> prepared statements. I add messages to these two statements and execute the
>> batches as shown below.
>>
>> for (AndesRemovableMetadata md : messagesToRemove) {
>> //add parameters to delete metadata
>> metadataRemovalPreparedStatement.setInt(1, queueID);
>> metadataRemovalPreparedStatement.setLong(2, md.getMessageID());
>> metadataRemovalPreparedStatement.addBatch();
>>
>> //add parameters to delete content
>> contentRemovalPreparedStatment.setLong(1, md.getMessageID());
>> contentRemovalPreparedStatment.addBatch();
>> }
>> metadataRemovalPreparedStatement.executeBatch();
>> contentRemovalPreparedStatment.executeBatch();
>> connection.commit();
>>
>>
>> This works fine in a normal scenario. But when I kill the MB node (which
>> acts as the SQL client) suddenly, it seems like the execution of the second
>> prepared statement is not completed. Meaning that, even though the metadata
>> is removed from the respective table, the content is not. When I
>> interchange the statement executions, metadata gets left in the metadata
>> table and content gets deleted.
>>
>> Could I please know what I'm doing wrong?
>>
>> Also, another solution to this would be to add a foreign key constraint
>> to the content table to enable cascade delete. Would that be a better
>> approach?
>>
>> Thank you
>> --
>> Sasikala Kottegoda
>> *Software Engineer*
>> WSO2 Inc., http://wso2.com/
>> lean. enterprise. middleware
>> Mobile: +94 774835928/712792401
>>
>> _______________________________________________
>> Dev mailing list
>> Dev@wso2.org
>> http://wso2.org/cgi-bin/mailman/listinfo/dev
>>
>>
>
>
> --
> Harsha Kumara
> Software Engineer, WSO2 Inc.
> Mobile: +94775505618
> Blog:harshcreationz.blogspot.com
>
--
Sasikala Kottegoda
*Software Engineer*
WSO2 Inc., http://wso2.com/
lean. enterprise. middleware
Mobile: +94 774835928/712792401
_______________________________________________
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev
