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